Solved

Split field into multiple rows

Posted on 2010-11-10
8
635 Views
Last Modified: 2012-05-10
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
Comment
Question by:angelnjj
  • 4
  • 3
8 Comments
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
use Split function
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
0
 
LVL 1

Author Comment

by:angelnjj
Comment Utility
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
 
LVL 1

Author Comment

by:angelnjj
Comment Utility
select resort, resv_name_id, allotment_header_id, grpvtrnt, dbo.split(products,',') as products from #products isn't working.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 35

Expert Comment

by:YZlat
Comment Utility
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:angelnjj
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now