Post reply

Column data to separate rows

  • January 13, 2006 at 3:27 pm

    #89231

    I have a column with comma delimited values that needs to have a separate row for each value.  I would like to know if there is a way to do it without using a CURSOR to parse out the strings.  Here is an example:

    SIDQstIDSomColDataValue
    15abc04,97
    25def03,04,07
    35ghi01
    45klm01,02,06,98

    Should be:

    SIDQstIDSomColDataValue
    15abc04
    15abc97
    25def03
    25def04
    25def07
    35ghi01
    45klm01
    45klm02
    45klm06
    45klm98

    I do have another table that has the QstID with all of the possible DataValue if that helps. 

    Let me know if you need more info. 

  • January 13, 2006 at 3:39 pm

    #614768

    What is the maximum number of values that can be in the DataValue column ?

  • January 13, 2006 at 3:45 pm

    #614769

    Well unfortunately that depends on the question. It can have at least 1 to x.  In this instance x can be the number of possible responses per question. 

    The original table has a row for each question.  Some questions have mutliple response possiblities where only some of them allow a single response. 

    I need to break up the column that contains the mutiple response values into separate rows.

  • January 15, 2006 at 9:12 am

    #614806

    It's easy with a Tally (or Numbers) table...

    --===== This is just part of the test setup...

    --===== If experimental tables exists, drop them

         IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

            DROP TABLE #yourtable

         -- This drops the temporary "Tally" or Numbers table.

         -- If you have a permanent one, you can delete this part.

         IF OBJECT_ID('TempDB..#Tally') IS NOT NULL

            DROP TABLE #Tally

    --===== This is just part of the test setup...

    --===== Create an experimental table with the data posted

     CREATE TABLE #yourtable (SID INT,QstID INT,SomCol VARCHAR(5),DataValue VARCHAR(20))

     INSERT INTO #yourtable (SID,QstID,SomCol,DataValue)

     SELECT 1,5,'abc','04,97' UNION ALL

     SELECT 2,5,'def','03,04,07' UNION ALL

     SELECT 3,5,'ghi','01' UNION ALL

     SELECT 4,5,'klm','01,02,06,98'

    --===== I recommend making a permanent Tally table from 1 to 9999

    --===== Create a temporary "Tally" table

         -- If you have a permanent "Tally" or "Numbers" table,

         -- you can delete this section.

     SELECT TOP 8000 IDENTITY(SMALLINT,1,1) AS N

       INTO #Tally

       FROM SysObjects so1,

            SysObjects so2

    --===== This is the "meat" of the solution which becomes pretty easy

         -- with a Tally table.

    --===== Convert the multiple value CSV column to multiple rows

         -- You could change this to a "SELECT/INTO" or "INSERT/SELECT" to

         -- put the results in a permanent table.

     SELECT SID,

            QstID,

            SomCol,

            SUBSTRING(','+mt.DataValue+',',N+1,CHARINDEX(',',','+mt.DataValue+',',N+1)-N-1) AS DataValue

       FROM #yourtable mt,

            #Tally t

      WHERE t.N < LEN(','+mt.DataValue+',')

        AND SUBSTRING(','+mt.DataValue+',',N,1) =','

    I strongly recommend making a permanent Tally table from 1 to 9999.  There are dozens of other things you can do to turn RBAR ("Row By Agonizing Row", a "Modenism") code into set-based code using the Tally table.

    Obviously, in the code above, you would substitute your real table for the temp table #yourtable and get rid of the condtional drops.

    And, it doesn't matter how many CSV values you have...

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

  • January 16, 2006 at 8:42 pm

    #615041

    Ok, then... you ask a question, I bust a hump giving you a pretty good answer... did it work for you or what?

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

  • January 17, 2006 at 9:22 am

    #615196

    Hi Jeff,

    Sorry I didn't respond sooner.  I have been swamped here and wasn't able to  check the boards sooner. 

    I came up with my own solution and was just about to post that I did, when I saw your replies.  I haven't given your solution a try yet.  I'm going to try a little later when I have a free moment. 

    Thank you for the time and effort you put in for me.  

    Here is the solution I came up with:

     SELECT  c.SY_TransactionID

     , c.SID

     , c.QID

     , c.SomVal

     , qf.formatvalue

    FROM c

    RIGHT OUTER JOIN qf ON qf.qstid = c.qid

     AND CHARINDEX(CASE WHEN LEN(qf.formatvalue) > 1 THEN '' ELSE '0' END + CAST(qf.formatvalue AS VARCHAR(2))

       , CASE WHEN LEN(c.DataValue) = 1 THEN '0' ELSE '' END + c.DataValue) > 0

          

    ORDER BY SID, c.QID, qf.formatvalue

    This is not exactly what I have but the gist of the query is here.  The QF table contains the questions and there valid DataValues.  For instance, Question 1 could have a valid value of 1,2,3,4,5.  So there would be 5 rows in QF for Question 1 with each value in its own row.

    I want to thank you again for your solution.  I'm actually going to try it as soon as I can. 

  • January 17, 2006 at 8:08 pm

    #615356

    Thanks for coming back, John...

    Yeah, I saw your original post where you said you had a table with all of the individual possible values.  Your solution looks as though it will work fine in the presence of that table.  I didn't do it that way, well, just because... what if such a table didn't exist?  Like I said, though, it looks like your solution will work as well...

    Based on you having such a table lookup table, here's another example how it could be done...

    SELECT  c.SY_TransactionID

     , c.SID

     , c.QID

     , c.SomVal

     , qf.formatvalue

    FROM c, qf 

    WHERE ','+c.CSVString+',' LIKE '%,'+qf.formatvalue+',%'

    ORDER BY SID, c.QID, qf.formatvalue

    It doesn't require a Tally table like my previous answer because the lookup table takes the place of a Tally table (kinda).

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

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

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