SQLTeam.com | Weblogs | Forums

Converting single row into multiple rows based on values in columns

Hi All.
I need help to convert set of columns into set of rows. What I mean. For instance table has:
1 152 ncvnvc gfg
2 256 ldkfjgdflkjg rtyrt
3 26 unkh 546s
4 5198 lkjsdlkj sdf45

as the result I would like to get:
1 152
1 ncvnvc
1 gfg
2 256
2 ldkfjgdflkjg
2 rtyrt
3 26
3 unkh
3 546s
4 5198
4 lkjsdlkj
4 sdf45

How to get that result if it possible. I will appreciate for explanation.


Very easy to do (and to do efficiently). I'll post code if you post directly usable sample data: CREATE TABLE and INSERT statements for the data shown.

Hi ScottPletcher. Thanks for replay.
Can you show code how it to do?


create table #eugz(id int, id2 int, boombam varchar(max))

insert into #eugz
select 1, 152, 'ncvnvc gfg' union 
select 2, 256, 'ldkfjgdflkjg rtyrt'

etc with the whole data set

Hi yosiasz.
I tried your code. The result is not like i expected
id id2 boombam
1 152 ncvnvc gfg
2 256 ldkfjgdflkjg rtyrt

But I would like to get multiple rows from single record


You asked "Can you show code how it to do?" The code I posted was an answer on how to do it that is how to provide sample data

1 Like


i tried to do this .. hope it helps :slight_smile: :slight_smile:

drop create data ...
drop table #data 

create table #data 
id int ,
string varchar(100)

insert into #data select 1,'152 ncvnvc gfg'
insert into #data select 2, '256 ldkfjgdflkjg rtyrt'
insert into #data select 3, '26 unkh 546s'
insert into #data select 4, '5198 lkjsdlkj sdf45'

select * from #data 
SQL ....
;WITH cte 
     AS (SELECT id, 
                Cast(LEFT(string, Charindex(' ', string + ' ') - 1) AS VARCHAR) 
                AS ok 
                Stuff(string, 1, Charindex(' ', string + ' '), '') 
                AS ok123 
         FROM   #data 
         UNION ALL 
         SELECT id, 
                Cast(LEFT(ok123, Charindex(' ', ok123 + ' ') - 1) AS VARCHAR), 
                Stuff(ok123, 1, Charindex(' ', ok123 + ' '), '') 
         FROM   cte 
         WHERE  ok123 > '') 
FROM   cte 


Based on the description, the values would seem to be separate columns, not one long column.

Not sure what you mean Scott
Please help me understand

using @JeffModen's awesome DelimitedSplit8K.

create table #eugz(id int, boomshakala varchar(max))

insert into #eugz
select 1 , '152 ncvnvc gfg' union
select 2 , '256 ldkfjgdflkjg rtyrt' union
select 3 , '26 unkh 546s' union
select 4 , '5198 lkjsdlkj sdf45' 

select * 
from #eugz e
cross apply [dbo].[DelimitedSplit8K](boomshakala, ' ') d
order by e.ID 

drop table #eug


Understood what you meant Scott..

Got you


if this is what you meant !!

drop create data .. Data different format
drop table #data 

create table #data 
id int ,
string1 varchar(100),
string2 varchar(100),
string3 varchar(100)

insert into #data select 1,'152','ncvnvc','gfg'
insert into #data select 2, '256','ldkfjgdflkjg','rtyrt'
insert into #data select 3, '26','unkh','546s'
insert into #data select 4, '5198','lkjsdlkj','sdf45'

select * from #data 
SQL ... for data different format
select id,string1 from #data 
union all 
select id,string2 from #data
union all 
select id,string3 from #data

SELECT id, string
FROM #data
CROSS APPLY ( VALUES(1, string1),(2, string2),(3, string3) ) AS data(string#, string)
ORDER BY id, string#