SQL query to populate one table row in multiple rows

Aishwarya Tripathi 21 Reputation points

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

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
    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)
    DROP TABLE #Tripathi

0 additional answers

Sort by: Most helpful