CODEWITHSUNDEEP
sqlsql-serversql-server-2005
TheSoftwareJedi
TheSoftwareJedi

Reputation: 35196

How do I expand comma separated values into separate rows using SQL Server 2005?

I have a table that looks like this:

ProductId, Color
"1", "red, blue, green"
"2", null
"3", "purple, green"

And I want to expand it to this:

ProductId, Color
1, red
1, blue
1, green
2, null
3, purple
3, green

Whats the easiest way to accomplish this? Is it possible without a loop in a proc?

Upvotes: 2

Views: 24412

Answers (8)

MelOS
MelOS

Reputation: 653

Why not use dynamic SQL for this purpose, something like this(adapt to your needs):

DECLARE @dynSQL VARCHAR(max)
SET @dynSQL = 'insert into DestinationTable(field) values'
select @dynSQL = @dynSQL + '('+ REPLACE(Color,',',''',''') + '),' from Table
SET @dynSql = LEFT(@dynSql,LEN(@dynSql) -1) -- delete the last comma
exec @dynSql

One advantage is that you can use it on any SQL Server version

Upvotes: 0

Hiroshi
Hiroshi

Reputation: 71

I arrived this question 10 years after the post. SQL server 2016 added STRING_SPLIT function. By using that, this can be written as below.

declare @product table
(
    ProductId int,
    Color     varchar(max)
);
insert into @product values (1, 'red, blue, green');
insert into @product values (2, null);
insert into @product values (3, 'purple, green');

select
    p.ProductId as ProductId,
    ltrim(split_table.value) as Color
from @product p
outer apply string_split(p.Color, ',') as split_table;

Upvotes: 1

nurettin
nurettin

Reputation: 11736

Just convert your columns into xml and query it. Here's an example. 

select 
    a.value('.', 'varchar(42)') c
from (select cast('<r><a>' + replace(@CSV, ',', '</a><a>') + '</a></r>' as xml) x) t1
cross apply x.nodes('//r/a') t2(a)

Upvotes: 0

KM.
KM.

Reputation: 103589

based on your tables:

create table test_table
(
     ProductId  int
    ,Color      varchar(100)
)

insert into test_table values (1, 'red, blue, green')
insert into test_table values (2, null)
insert into test_table values (3, 'purple, green')

create a new table like this:

CREATE TABLE Numbers
(
    Number  int   not null primary key
)

that has rows containing values 1 to 8000 or so.

this will return what you want:

EDIT
here is a much better query, slightly modified from the great answer from @Christopher Klein:

I added the "LTRIM()" so the spaces in the color list, would be handled properly: "red, blue, green". His solution requires no spaces "red,blue,green". Also, I prefer to use my own Number table and not use master.dbo.spt_values, this allows the removal of one derived table too.

SELECT
    ProductId, LEFT(PartialColor, CHARINDEX(',', PartialColor + ',')-1) as SplitColor
    FROM (SELECT 
              t.ProductId, LTRIM(SUBSTRING(t.Color, n.Number, 200)) AS PartialColor
              FROM test_table             t
                  LEFT OUTER JOIN Numbers n ON n.Number<=LEN(t.Color) AND SUBSTRING(',' + t.Color, n.Number, 1) = ','
         ) t

EDIT END

SELECT
    ProductId, Color --,number
    FROM (SELECT
              ProductId
                  ,CASE
                       WHEN LEN(List2)>0 THEN LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1)))
                       ELSE NULL
                   END AS Color
                  ,Number
              FROM (
                       SELECT ProductId,',' + Color + ',' AS List2
                           FROM test_table
                   ) AS dt
                  LEFT OUTER JOIN Numbers n ON (n.Number < LEN(dt.List2)) OR (n.Number=1 AND dt.List2 IS NULL)
              WHERE SUBSTRING(List2, number, 1) = ',' OR List2 IS NULL
         ) dt2
    ORDER BY ProductId, Number, Color

here is my result set:

ProductId   Color
----------- --------------
1           red
1           blue
1           green
2           NULL
3           purple
3           green

(6 row(s) affected)

which is the same order you want...

Upvotes: 5

Christopher Klein
Christopher Klein

Reputation: 2793

You can try this out, doesnt require any additional functions:

declare @t table (col1 varchar(10), col2 varchar(200))
insert @t
          select '1', 'red,blue,green'
union all select '2', NULL
union all select '3', 'green,purple'


select col1, left(d, charindex(',', d + ',')-1) as e from (
    select *, substring(col2, number, 200) as d from @t col1 left join
        (select distinct number from master.dbo.spt_values where number between 1 and 200) col2
        on substring(',' + col2, number, 1) = ',') t

Upvotes: 4

chilltemp
chilltemp

Reputation: 8962

Take a look at this function. I've done similar tricks to split and transpose data in Oracle. Loop over the data inserting the decoded values into a temp table. The convent thing is that MS will let you do this on the fly, while Oracle requires an explicit temp table.

MS SQL Split Function
Better Split Function

Edit by author: This worked great. Final code looked like this (after creating the split function):

select pv.productid, colortable.items as color
from product p 
    cross apply split(p.color, ',') as colortable

Upvotes: 9

casperOne
casperOne

Reputation: 74530

I would create a CLR table-defined function for this:

http://msdn.microsoft.com/en-us/library/ms254508(VS.80).aspx

The reason for this is that CLR code is going to be much better at parsing apart the strings (computational work) and can pass that information back as a set, which is what SQL Server is really good at (set management).

The CLR function would return a series of records based on the parsed values (and the input id value).

You would then use a CROSS APPLY on each element in your table.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

Fix your database if at all possible. Comma delimited lists in database cells indicate a flawed schema 99% of the time or more.

Upvotes: 0

Related Questions