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.

Thanks.

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?

Thanks

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

Thanks

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

hi

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

drop create data ...
drop table #data 
go 

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

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'
go

select * from #data 
go
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 > '') 
SELECT id, 
       ok 
FROM   cte 
ORDER  BY id 

go
result

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

image

Understood what you meant Scott..

Got you

:slightly_smiling_face::slightly_smiling_face:

if this is what you meant !!

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

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

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'
go

select * from #data 
go
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#