[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Split field into multiple rows

Posted on 2010-11-10
8
Medium Priority
?
642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 34105147
use Split function
0
 
LVL 35

Expert Comment

by:YZlat
ID: 34105158
0
 
LVL 1

Author Comment

by:angelnjj
ID: 34105294
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:angelnjj
ID: 34105299
select resort, resv_name_id, allotment_header_id, grpvtrnt, dbo.split(products,',') as products from #products isn't working.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 34105332
0
 
LVL 35

Expert Comment

by:YZlat
ID: 34105360
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 34105426
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
ID: 34106330
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Copy Database Wizard 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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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