SQL row-to-column Conversion

The first is SQL server usage:

The structure of a table is as follows: ID name contetid
1 Test 1
2 test1 1
3 Test2 1
4 test3 2
5 test4 2
Add its records to another table with the following structure:

Contetid names
1 test, test1, Test2
2 test3, test4

 

If Object_id ( ' A ' , ' U ' ) Is Not Null Drop Table A
Go
Create Table A
(
ID Int , Name Nvarchar ( 10 ), Contetid Int
)
Go
Insert Into A
Select 1 , ' Test ' , 1 Union All
Select 2 , ' Test1 ' , 1 Union All
Select 3 , ' Test2 ' , 1 Union All
Select 4 , ' Test3 ' , 2 Union All
Select 5 , ' Test4 ' , 2
Go

Select Distinct Contetid,
Name = Stuff (( Select ' , ' + Name From A As BWhere A. contetid = B. contetid For XML Path ( '' )), 1 , 1 , '' )
From A As A
/*
Contetid name
---------------------------
1 test, test1, Test2
2 test3, test4
*/

-- Insert the preceding result into another table.

Insert Into Othertb
Select Distinct Contetid,
Name = Stuff (( Select ' , ' + Name From A As B Where A. contetid = B. contetid For XML Path ( '' )), 1 ,1 , '' )
From A As A

Core functions:

1.Select ','+NameForXML Path (''): Generate, test, test1, Test2.ForXML Path is used to produce XML format data. For details, see:

Http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

2.Stuff:

The stuff (expressionshortstr, startindex, lengthint, numeric) function has four parameters. The function is to delete lengthint characters from the startindex position in expressionshortstr and insert expression2 to the startindex position in expressionshortstr.

Here, it is used to remove the first number in the string generated by the XML Path.

 

In Oracle, there is no for XML Path, but more convenient functions are provided:

Function wm_concat (column name). This function can separate column values with "," and display them as a row.

Http://blog.itpub.net/post/42245/522757

Http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html