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
hi
i tried to do this .. hope it helps 
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
;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
Based on the description, the values would seem to be separate columns, not one long column.
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 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
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#