Transfer columns into rows in sql server management studio -

table 1

column_pk  num  name   id -1   id-2     id-3 1          145  sam    12345   null     34527 2          187  tom    null    76544    null 3          567  david  78965   43215    89765 4          675  john   null    null     null 

as shown in above table given column_pk id-1, id-2, id-3 can have values or can null

now have move id-1, id-2, id-3 table2 rows follows:

• since sam has 2 id’s: id-1 & id-3 same num 145 sam has 2 rows

• since david has 3 id’s in table 1, table 2 has 3 rows david same num 567

table 2

column_pk  num    name     id 1          145    sam      12345 2          145    sam      34527 3          187    tom      76544 4          567    david    78965 5          567    david    43215           6          567    david    89765 

• i tried using few update statements, statements useful limited data only. can please me when there huge transfer of data.

since using sql server 2008, can unpivot data using cross apply , values. process takes column values , converts them rows:

select row_number() over(order t1.column_pk) column_pk,   t1.num,    t1.name,    c.value table1 t1 cross apply (   values      ('id1', id1),     ('id2', id2),     ('id3', id3) ) c (col, value) c.value not null; 

see sql fiddle demo