SQL query to populate one table row in multiple rows

Aishwarya Tripathi 21 Reputation points
2020-10-06T21:40:10.23+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,071 Reputation points MVP
    2020-10-06T21:54:34.38+00:00
    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
    

0 additional answers

Sort by: Most helpful