[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

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
0
angelnjj
Asked:
angelnjj
  • 4
  • 3
1 Solution
 
YZlatCommented:
use Split function
0
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
angelnjjAuthor Commented:
select resort, resv_name_id, allotment_header_id, grpvtrnt, dbo.split(products,',') as products from #products isn't working.
0
 
YZlatCommented:
0
 
ralmadaCommented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now