SQL help, how to split one row into many rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL help, how to split one row into many rows

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-10-20 : 20:36:02

Hi,

I got a question on SQL. One table, structure like this: (2 columns)

A 123,456,789
B 012,345
C 678,901,234

How to get the final result like below:

A 123,
A 456,
A 789
B 012,
B 345,
C 678,
C 901,
C 234

Do I have to use PL/SQL? If so, how should I do it? Appreciate any thought you have on it!

Cheers,
Ken

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-20 : 20:40:02

Nope:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56740

Couple of links in there you can use. The tally table technique should work in Oracle, but you may have to change the SubString and CharIndex functions to Oracle equivalents.

Beaumondo
Starting Member

2 Posts

Posted - 2005-10-22 : 12:18:49

Hi

I create the tables "Category" and "Number" and used substring and union to split the data.

SELECT Category, SUBSTRING(Number, 1, 4) AS Number
FROM SQLSPLIT
UNION
SELECT Category, SUBSTRING(Number, 5, 4)
FROM SQLSPLIT
UNION
SELECT Category, SUBSTRING(Number, 9, 4)
FROM SQLSPLIT

It's a bit messy and i'm sure there's a better way to do it but i need to go out now so i'll have another look later or tomorrow.

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 00:54:55

Search this post for "CSV" and "SPLIT"

See also Best split function

Kristen

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-10-24 : 14:55:20

Thanks very much for above responses!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources