SQL Query - Help

[TimHop12]

I have the following data in a table T1:

Col1 Col2 Col3 Col4 Col5
10 x 20 a b
10 x 20 a1 b1
10 y 20 c d

Now looking at this table T1, which I am generating dynamically as a temp-table, I want to "insert" data in a different table T2 as rows, in the following fashion:

Single_Column
10 x
20 a b
20 a1 b1
10 y
20 c d

To get you familiarity with the format of the data in T2, this would be more like an XML structure, where "10 x" is the data in the parent-node, and "20 a b", "20 a1 b1" are the two child nodes. But in the table T2 shown above, this child-node data will be in different rows, just below the parent-node data row.

Please advice about the insert statement.

Thanks.

[camel]

Code below will do what you ask, though I wonder a little why you need to do this..If data is hierachical the db schema should be designed to store it as such e.g. with parent-child id relations rather than having to sort it out after the event

/* Assemble Test Data */
CREATE TABLE #OldStructure
(ParentID int IDENTITY(1,1), Col1 int, Col2 char(1), Col3 int, Col4 varchar(2), Col5 varchar(3))
 
INSERT #OldStructure(Col1,Col2,Col3,Col4,Col5)
SELECT 10,'x',20,'a','b'
INSERT #OldStructure(Col1,Col2,Col3,Col4,Col5)
SELECT 10,'x',20,'a1','b1'
INSERT #OldStructure(Col1,Col2,Col3,Col4,Col5)
SELECT 10,'y',20,'c','d'
 
/* Put Data in new table as desired */
CREATE TABLE #NewStructure
(SingleColumn varchar(20), ParentNode varchar(20), OriginalOrder int)
 
INSERT #NewStructure(SingleColumn, ParentNode)
SELECT    CONVERT(varchar(8),Col1) + Col2, CONVERT(varchar(8),Col1) + Col2
FROM    #OldStructure
GROUP BY Col1, Col2
 
INSERT  #NewStructure(SingleColumn, ParentNode, OriginalOrder)
SELECT    CONVERT(varchar(8),Col3) + Col4 + Col5, CONVERT(varchar(8),Col1) + Col2, ParentID  
FROM    #OldStructure O
 
SELECT SingleColumn FROM #NewStructure
ORDER BY ParentNode, OriginalOrder