Post reply

Split coulumn value

  • February 25, 2011 at 11:19 am

    #145083

    Hi from a result like this

    NumCodeStart DateEnd Date

    204-201ABCDF2004-04-04NULL

    204-203ABCDF2004-07-18NULL

    204-205QWERT,ASDFG2005-12-05NULL

    204-206YUIOP,GHJKL2006-05-11NULL

    I wish to get this :

    NumCodeStart DateEnd Date

    204-201ABCDF2004-04-04NULL

    204-203ABCDF2004-07-18NULL

    204-205QWERT2005-12-05NULL

    204-205ASDFG2005-12-05NULL

    204-206YUIOP2006-05-11NULL

    204-206 GHJKL2006-05-11NULL

    The column that sometimes has coma seperated values are then split to creat another record with same Num, Start Date and End Date.

  • February 25, 2011 at 11:47 am

    #1291703

    You need to utilize the DelimitedSplit8K function to split the delimited list apart.

    You would use it like so:

    SELECT Num, Item AS Code, [Start Date], [End Date]

    FROM #TEST t

    CROSS APPLY DelimitedSplit8K(t.Code, ',') ds

    ORDER BY t.Num, ds.ItemNumber

    Here is the latest version of the Delimited Split Function

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 25, 2011 at 3:04 pm

    #1291800

    That did niot do the trick!

    I found my answer here -->

    Thanks anyway!

  • February 25, 2011 at 6:56 pm

    #1291863

    infodemers (2/25/2011)

    That did niot do the trick!

    I found my answer here -->

    Thanks anyway!

    Are you sure? When I run this code:

    DECLARE @test-2 TABLE (Num CHAR(7), Code VARCHAR(50), [Start Date] DATETIME, [End Date] DATETIME);

    INSERT INTO @test-2

    SELECT '204-201','ABCDF', '2004-04-04', NULL UNION ALL

    SELECT '204-203','ABCDF','2004-07-18', NULL UNION ALL

    SELECT '204-205','QWERT,ASDFG','2005-12-05', NULL UNION ALL

    SELECT '204-206','YUIOP,GHJKL','2006-05-11', NULL ;

    SELECT Num, Item AS Code, [Start Date], [End Date]

    FROM @test-2 t

    CROSS APPLY DelimitedSplit8K(t.Code, ',') ds

    ORDER BY t.Num, ds.ItemNumber

    I get exactly the desired results:

    Num Code Start Date End Date

    ------- ----- ----------------------- -----------------------

    204-201 ABCDF 2004-04-04 00:00:00.000 NULL

    204-203 ABCDF 2004-07-18 00:00:00.000 NULL

    204-205 QWERT 2005-12-05 00:00:00.000 NULL

    204-205 ASDFG 2005-12-05 00:00:00.000 NULL

    204-206 YUIOP 2006-05-11 00:00:00.000 NULL

    204-206 GHJKL 2006-05-11 00:00:00.000 NULL

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 27, 2011 at 5:07 pm

    #1292092

    infodemers (2/25/2011)

    That did niot do the trick!

    I found my answer here -->

    Thanks anyway!

    Could you post the code that didn't do the trick? Thanks.

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

  • February 28, 2011 at 11:56 am

    #1292501

    I would also very much like to see the code that didn't work. I've been using Wayne's technique and Jeff's Delimited8KSplit for a while now and those techniques have always worked.

    Todd Fifield

  • March 1, 2011 at 7:15 am

    #1292897

    Hi all,

    I apologize, the solution suggested by Wayne is working fine.

    I made a mistake with that part of the solution (t.Code, ',') ds.

    :Whistling:

  • March 1, 2011 at 7:56 am

    #1292942

    infodemers (3/1/2011)

    Hi all,

    I apologize, the solution suggested by Wayne is working fine.

    I made a mistake with that part of the solution (t.Code, ',') ds.

    :Whistling:

    Thanks for the feedback on this... it would have been quite a shock to many people if this routine wasn't working correctly!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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