SQL query to populate one table row in multiple rows

Question

Data in first table

ID col1 col2 col3
1001 aa bb cc
1002 xx yy zz

read data from above table to insert in second table as follows:
ID value sequence
1001 aa 1
1001 bb 2
1001 cc 3
1002 xx 1
1002 yy 2
1003 zz 3

Answer 1

CREATE TABLE #Tripathi(ID int NOT NULL,
                       col1 varchar(23) NULL,
                       col3 varchar(23) NULL,
                       col2 varchar(23) NULL)
INSERT #Tripathi(ID, col1, col2, col3)
   VALUES(1001, 'aa', 'bb', 'cc'),
         (1002, 'xx', 'yy', 'yy')

SELECT t.ID, V.value, V.Sequence
FROM   #Tripathi t
CROSS  APPLY (VALUES(1, col1), (2, col2), (3, col3)) AS V(value, Sequence)
go
DROP TABLE #Tripathi