Split field into multiple rows
Hello All,
I have the following data:
create table #products
(resort varchar(20)
, resv_name_id int
, allotment_header_id int
, grpvtrnt varchar(1)
, products varchar(200)
)
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34202, 16711, 'G', 'A, A, B, C, D, E')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34203, 16711, 'G', 'A, B, C')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34204, 16711, 'G', 'A, B, C, D')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34205, 16711, 'G', 'A')
select * from #products
What I need at the end is one row per distinct product by resv_name_id
resort resv_name_id allotment_header_id grpvtrnt products
MTWASH 34202 16711 G A
MTWASH 34202 16711 G B
MTWASH 34202 16711 G C
MTWASH 34202 16711 G D
MTWASH 34202 16711 G E
MTWASH 34203 16711 G A
MTWASH 34203 16711 G B
MTWASH 34203 16711 G C
MTWASH 34204 16711 G A
MTWASH 34204 16711 G B
MTWASH 34204 16711 G C
MTWASH 34204 16711 G D
MTWASH 34205 16711 G A
I have the following data:
create table #products
(resort varchar(20)
, resv_name_id int
, allotment_header_id int
, grpvtrnt varchar(1)
, products varchar(200)
)
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34202, 16711, 'G', 'A, A, B, C, D, E')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34203, 16711, 'G', 'A, B, C')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34204, 16711, 'G', 'A, B, C, D')
insert into #products (resort, resv_name_id , allotment_header_id , grpvtrnt , products) VALUES ('MTWASH', 34205, 16711, 'G', 'A')
select * from #products
What I need at the end is one row per distinct product by resv_name_id
resort resv_name_id allotment_header_id grpvtrnt products
MTWASH 34202 16711 G A
MTWASH 34202 16711 G B
MTWASH 34202 16711 G C
MTWASH 34202 16711 G D
MTWASH 34202 16711 G E
MTWASH 34203 16711 G A
MTWASH 34203 16711 G B
MTWASH 34203 16711 G C
MTWASH 34204 16711 G A
MTWASH 34204 16711 G B
MTWASH 34204 16711 G C
MTWASH 34204 16711 G D
MTWASH 34205 16711 G A
YZlat
use Split function
ASKER
I found that, but can't figure out how to incorporate into my select * from #products since it needs the split to be in the from clause.
Original:
select top 10 * from dbo.split('Chennai,Bangalo
In select resort, resv_name_id, allotment_header_id, grpvtrnt, products from #products, where would I put the dbo.split?
Original:
select top 10 * from dbo.split('Chennai,Bangalo
In select resort, resv_name_id, allotment_header_id, grpvtrnt, products from #products, where would I put the dbo.split?
ASKER
select resort, resv_name_id, allotment_header_id, grpvtrnt, dbo.split(products,',') as products from #products isn't working.
here is something that's almost identical to what you are doing
http://www.eggheadcafe.com/software/aspnet/31741612/split-column-into-multiple-rows-and-also-keep-other-columns-as-wel.aspx
http://www.eggheadcafe.com/software/aspnet/31741612/split-column-into-multiple-rows-and-also-keep-other-columns-as-wel.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you...how to use it was what I was missing - didn't understand need for cross apply, thanks for the example (used a different function).