SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

singularity
Posting Yak Master

151 Posts

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

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,
etc.

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.





dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 09/21/2007 :  21:38:13  Show Profile  Visit dinakar's Homepage  Reply with Quote
Check if this helps: http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 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
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC


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...

SELECT User_ID, 
        Profile,
        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
                        END
   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

singularity
Posting Yak Master

151 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

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/23/2007 :  02:30:49  Show Profile  Reply with Quote
See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting%20delimited%20lists,Best%20split%20functions
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 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

USA
649 Posts

Posted - 09/23/2007 :  11:03:45  Show Profile  Reply with Quote
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  
 New 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.08 seconds. Powered By: Snitz Forums 2000