Click here to Skip to main content
14,699,563 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Input Table
---------

ID N1 N2 N3 N4
1 a b c d
2 x y z NULL
3 m n NULL NULL

Output
-------

ID Name
1 a
1 b
1 c
1 d
2 x
2 y
2 z
3 m
3 n
Posted
Updated 5-Jan-15 19:59pm
v2

Try this !..

create table #tbl_split (id int identity(1,1),N1 varchar(100),N2 varchar(100),N3 varchar(100),N4 varchar(100))

insert into #tbl_split values('a','b','c','d')
insert into #tbl_split values('x','y','z','')
insert into #tbl_split values('m','n','','')

with cte as
(
select unpvt.id,unpvt.value from #tbl_split
unpivot (value for attribute in (N1,N2,N3,N4))unpvt)
select id,value from cte where value!=''
   
Comments
King Fisher 6-Jan-15 6:37am
   
good Job ;)
You can go for Unpivoting to achieve solution.

For some detail understanding follow the link:

Pivoting and Unpivoting

Hope it helps :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900