Converting a single row into Multiple rows

Question

Post reply

Converting a single row into Multiple rows

SQLMAIN

SSC Eights!

Points: 936
More actions
July 27, 2011 at 2:04 pm

#100404

I have a situation where a table row has a column that carries multiple values separated by commas. I want to split such rows into multiple rows each carrying one of the column values. here is an example
NAME IDS
ABC 123456, 234651, 345161
(6 byte vals w commas in between)
Need output as
ABC 123456
ABC 234651
ABC 345161
CREATE TABLE MULT_VALS (
NAME VARCHAR(100),
IDS VARCHAR(100)
)
INSERT INTO MULT_VALS VALUES ('ABC', '123456, 234651, 345161')
INSERT INTO MULT_VALS VALUES ('XYZ', '323457')
INSERT INTO MULT_VALS VALUES ('JHK', '245673, 342892')
INSERT INTO MULT_VALS VALUES ('LKM', '895688, 993722, 342822, 383535')
INSERT INTO MULT_VALS VALUES ('KKS', '895688')
Need the output in a table that is
NAME varchar(100)
ID CHAR(6)
Above will result in 11 rows.
Any help will be appreciated.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Search this site for "DelimitedSplit8k" (Rev 06 is the current version AFAIK).

The following query will return the expected result

SELECT c.name, ID = LTRIM(split.Item)

FROM MULT_VALS c

CROSS APPLY dbo.DelimitedSplit8k(c.IDS,',') split

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLMAIN SSC Eights! Points: 936 More actions · Answer 2

I am on SS2000.... Any solution that will work here.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

LutzM (7/27/2011)
Search this site for "DelimitedSplit8k" (Rev 06 is the current version AFAIK).
The following query will return the expected result
SELECT c.name, ID = LTRIM(split.Item)
FROM MULT_VALS c
CROSS APPLY dbo.DelimitedSplit8k(c.IDS,',') split

This is the 2K forum, Lutz. Probably not going to work here. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

SQLMAIN (7/28/2011)
I am on SS2000.... Any solution that will work here.
Thanks

Yes... there is a solution. Some very important high performance improvements have been made in the last couple of months to solutions to 2k5 and above... I need to try to echo those improvements in a 2k compatible "Tally Table" splitter for you. I'll try to get to that tonight. In the meantime, if you find a CSV splitter with a While Loop in it or any splitter (including one that uses a "Tally" or "Numbers" table) that concatenates a delimiter to the original string, I strongly recommend that you DON'T use it because of the hidden performance problems that will rear up and bite you in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLMAIN SSC Eights! Points: 936 More actions · Answer 5

This is a one time thing and I have to finish today.. so after some thought process.. this what I have come up with....

alter table mult_vals add cnt smallint

--This tells me how many IDS are there

update mult_vals

set cnt = LEN(IDS) - LEN(REPLACE(IDS,',','')) + 1

--Then move diff sets to diff tables

select *

into #one

from mult_vals

where cnt = 1

select *

into #two

from mult_vals

where cnt = 2

select *

into #thr

from mult_vals

where cnt = 3

select *

into #for

from mult_vals

where cnt = 4

--Next union these together...

select * from #ONE

UNION

select name, substring(IDS, 1, 6) from #TWO

UNION

select name, substring(IDS, 9, 6) from #TWO

UNION

select name, substring(IDS, 1, 6) from #THR

UNION

select name, substring(IDS, 9, 6) from #THR

UNION

select name, substring(IDS, 17, 6) from #THR

UNION

select name, substring(IDS, 1, 6) from #FOR

UNION

select name, substring(IDS, 9, 6) from #FOR

UNION

select name, substring(IDS, 17, 6) from #FOR

UNION

select name, substring(IDS, 25, 6) from #FOR

nishav2 SSCrazy Points: 2224 More actions · Answer 6

Hi ,

I am having similar kind of requirement.

I have a table.

create table dbo.test(ID int,Price decimal(18,9), Qty decimal(18,9))

Insert into dbo.test values(1,5000,5000000)

Insert into dbo.test values(2,3000,50000000)

Insert into dbo.test values(3,100,50000)

I need the output in the below format

ID PRICE QTY

1 5000 5000000

2-1 30 5000000

2-2 30 5000000

2-3 30 5000000

2-4 30 5000000

2-5 30 5000000

2-6 30 5000000

2-7 30 5000000

2-8 30 5000000

2-9 30 5000000

2-10 30 5000000

3 100 50000

So the Qty limit is : 5,000000

First record is equal to the limit.So it can appear in the same format as stored in table.

In Second Record -Qty is 10 times greater than our limit.So all the columns are splitted as shown above

Third Row - It is within the limit we have set for Qty.So it can appear in the same format as stored in table.

Any help is appreciated.

Thanks

Nisha V Krishnan

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

nishav2 (10/1/2014)
Hi ,
I am having similar kind of requirement.
I have a table.
create table dbo.test(ID int,Price decimal(18,9), Qty decimal(18,9))
Insert into dbo.test values(1,5000,5000000)
Insert into dbo.test values(2,3000,50000000)
Insert into dbo.test values(3,100,50000)
I need the output in the below format
ID PRICE QTY
1 5000 5000000
2-1 30 5000000
2-2 30 5000000
2-3 30 5000000
2-4 30 5000000
2-5 30 5000000
2-6 30 5000000
2-7 30 5000000
2-8 30 5000000
2-9 30 5000000
2-10 30 5000000
3 100 50000
So the Qty limit is : 5,000000
First record is equal to the limit.So it can appear in the same format as stored in table.
In Second Record -Qty is 10 times greater than our limit.So all the columns are splitted as shown above
Third Row - It is within the limit we have set for Qty.So it can appear in the same format as stored in table.
Any help is appreciated.
Thanks
Nisha V Krishnan

You should start your own threads instead of hijacking a number of other much older threads.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/