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
LVL 1
angelnjjAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
First create the split function below, then use it in your query like this:
select a.*, b.Value from #products a
cross apply dbo.parmstolist(a.products, ',') b

create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) ) 
returns @result TABLE (Value varchar(500), rn int identity) 
AS 
begin 
declare @dx varchar(9) 

DECLARE @TempList table 
( 
Value varchar(500) 
) 

if @delimiter is null set @delimiter = ' ' 
if len(@delimiter) < 1 set @delimiter = ' ' 
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1) 

DECLARE @Value varchar(8000), @Pos int 

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

IF REPLACE(@Parameters, @delimiter, @dx) <> '' 
BEGIN 
WHILE @Pos > 0 -- AND @Loops < 100 
BEGIN 
--set @loops = @loops + 1 
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1))) 
IF @Value <> '' 
BEGIN 
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion 
END 
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000) 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

END 
END 
INSERT @result 
SELECT value 
FROM @TempList 
RETURN 
END

Open in new window

0
 
YZlatCommented:
use Split function
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
angelnjjAuthor Commented:
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,Bangalore,Mumbai',',')

In select resort, resv_name_id, allotment_header_id, grpvtrnt, products from #products, where would I put the dbo.split?
0
 
angelnjjAuthor Commented:
select resort, resv_name_id, allotment_header_id, grpvtrnt, dbo.split(products,',') as products from #products isn't working.
0
 
YZlatCommented:
0
 
angelnjjAuthor Commented:
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).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.