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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Parsing a Comma-Separated Field into Rows
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

153 Posts

Posted - 09/21/2007 :  19:23:21  Show Profile  Reply with Quote

I have a table that's laid out the following way:

user_id profile profile_value
1 1 ,
1 2 ,7,
2 1 ,6,9,11,
2 2 ,8,10,

I need to convert it to look like this:

user_id profile profile_value
1 1
1 2 7
2 1 6
2 1 9
2 1 11
2 2 8
2 2 10

Does anyone have the code for doing this?

Thanks in advance.

Flowing Fount of Yak Knowledge

2507 Posts

Posted - 09/21/2007 :  21:38:13  Show Profile  Visit dinakar's Homepage  Reply with Quote
Check if this helps:

Dinakar Nethi
Life is short. Enjoy it.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 09/21/2007 :  22:56:51  Show Profile  Reply with Quote
Finally... glad to see someone try to normalize a CSV column.

There are a lot of methods to do this using While loops, cursors, and all manner of other SQL "sins" and, despite the very long code they produce (both in time and length), they may still not do what you want them to. Functions can be just as bad in both areas.

So, what to do. First, you need to make a Numbers or "Tally" table which contains nothing more than a well indexed single column of sequential numbers. When you join to it (usually using a cross join), it acts very much like the ROWNUM feature of SQL Server 2005. I call it a "Tally" table because I use it to count or "Tally" and it sounds cooler than just a "Numbers" table. It has many, many uses and normalize a CSV column for a whole table is just one of them. Here's how to build a Tally table... don't blink...

--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally

--===== Allow the general public to use it

After that, life becomes ever so simple... first, let's pretend this is your table...

--===== Create a test table... THIS IS NOT PART OF THE SOLUTION!  IT's JUST FOR DEMO!
 CREATE TABLE #yourtable (User_ID INT, Profile INT, Profile_Value VARCHAR(200))
 INSERT INTO #yourtable
        (User_ID ,Profile, Profile_Value)
 SELECT 1,1,','        UNION ALL
 SELECT 1,2,',7,'      UNION ALL
 SELECT 2,1,',6,9,11,' UNION ALL
 SELECT 2,2,',8,10,'

SELECT * FROM #yourtable

All we need to do to split the table as you've requested, is the following simple code... and, I do mean simple...

        Profile_Value = CASE
                            WHEN y.Profile_Value <> ','
                            THEN SUBSTRING(y.Profile_Value,t.N+1,CHARINDEX(',',y.Profile_Value,t.N+1)-t.N-1)
                        ELSE NULL
   FROM #yourtable y,
        dbo.Tally t
  WHERE (t.N < LEN(y.Profile_Value) OR Profile_Value = ',')
    AND SUBSTRING(y.Profile_Value,t.N,1) = ','

Try it... lemme know if it does the trick for ya...

--Jeff Moden

Edited by - Jeff Moden on 09/21/2007 22:57:54
Go to Top of Page

Posting Yak Master

153 Posts

Posted - 09/22/2007 :  18:42:26  Show Profile  Reply with Quote
Jeff, thank you so much. Your solution worked great!
Go to Top of Page


United Kingdom
22859 Posts

Posted - 09/23/2007 :  02:30:49  Show Profile  Reply with Quote
See also:,Best%20split%20functions
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 09/23/2007 :  11:02:25  Show Profile  Reply with Quote
Heh... oh yeah, I forget about that link. But, even the one I posted on that link doesn't show how to split a whole table like the code above does...

--Jeff Moden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 09/23/2007 :  11:03:45  Show Profile  Reply with Quote
Originally posted by singularity

Jeff, thank you so much. Your solution worked great!

You are most welcome. Thank you for the feedback...

--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000