Split Delimited String Across Fixed Number Of Columns

  • I have the following in a table and need to transfer the single delimited string out into 99 Columns.

    I'm afraid that the 99 Columns is a fixed template but i know from experience of the dataset that we will never reach 99 so I WILL need all 99 Column sin the output but know that they will not all be filled.

    Please find my demo script below along with a sample if the expected output.

    Thanks in advance as always for any help received.

    CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )

    INSERT INTO #temptable

    VALUES

    ( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )

    SELECT * FROM #temptable

    DROP TABLE #temptable

    --RESULTS

    Diag_1Diag_2Diag_3Diag_4Diag_5Diag_6Diag_7Diag_8Diag_9Diag_10Diag_11Diag_12Diag_13Diag_14Diag_15Diag_16….To 99

    I64XZ755F03XM819Z755F03XM819Z755

    F03XM819Z755F03XM819Z755F03XM819Z755F03XM819Z755

    E110I10XZ755F03XM819Z755F03XM819Z755

  • Partial solution. Will only work if all elements are four characters long (which they are in your sample data).

    SELECT

    SUBSTRING(Diagnoses_For_Spell,1,4)

    ,SUBSTRING(Diagnoses_For_Spell,6,4)

    ,SUBSTRING(Diagnoses_For_Spell,11,4)

    ,SUBSTRING(Diagnoses_For_Spell,16,4)

    ,SUBSTRING(Diagnoses_For_Spell,21,4)

    ,SUBSTRING(Diagnoses_For_Spell,26,4)

    ,SUBSTRING(Diagnoses_For_Spell,31,4)

    ,SUBSTRING(Diagnoses_For_Spell,36,4)

    ,SUBSTRING(Diagnoses_For_Spell,41,4)

    ,SUBSTRING(Diagnoses_For_Spell,46,4)

    FROM #temptable

    John

  • First you need to allocate a unique number to each row

    Second use Jeff Moden's DelimitedSplit8K function (here) to split the data

    Third use PIVOT or CASE statements to pivot the data into columns

    *Edited* John's solution would be neater

    Should think more before posting 🙁

    Far away is close at hand in the images of elsewhere.
    Anon.

  • here's an example that i just slapped together that does the first nine columns. using DelimitedSplit8K and the PIVOT operator.

    does this help?

    /*

    --Results

    Diagnoses_For_Spell 1 2 3 4 5 6 7 8 9

    E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755 E110 I10X Z755 F03X M819 Z755 F03X M819 Z755

    F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755 F03X M819 Z755 F03X M819 Z755 F03X M819 Z755

    I64X,Z755,F03X,M819,Z755,F03X,M819,Z755 I64X Z755 F03X M819 Z755 F03X M819 Z755 NULL

    */

    CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )

    INSERT INTO #temptable

    VALUES

    ( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )

    WITH MyCTE

    AS

    (

    select *

    FROM #temptable T1

    CROSS APPLY dbo.DelimitedSplit8K(T1.[Diagnoses_For_Spell],',') dl

    )

    select * from

    ( select * from MyCTE where ItemNumber <= 9 ) pivot_handle

    pivot

    (MAX([Item]) for ItemNumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) pivot_table

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I wouldn't want to write 99 columns, so I would use a dynamic approach for this.

    DECLARE @SQL nvarchar(max);

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    cteTally(n) AS(

    SELECT TOP (99)

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E a, E b

    )

    SELECT @SQL = N'SELECT ' +

    STUFF(( SELECT N',MAX( CASE WHEN ItemNumber = ' + CAST( n AS nvarchar(2)) + N' THEN Item END) AS Diag_' + CAST( n AS nvarchar(2)) + NCHAR(10)

    FROM cteTally

    ORDER BY n

    FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') +

    'FROM #tempTable t

    CROSS APPLY dbo.DelimitedSplitN4K( t.[Diagnoses_For_Spell], '','')s

    GROUP BY t.[Diagnoses_For_Spell];'

    EXEC sp_executesql @SQL;

    References:

    DelimitedSplitN4K: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (5/20/2016)


    I wouldn't want to write 99 columns, so I would use a dynamic approach for this.

    DECLARE @SQL nvarchar(max);

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    cteTally(n) AS(

    SELECT TOP (99)

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E a, E b

    )

    SELECT @SQL = N'SELECT ' +

    STUFF(( SELECT N',MAX( CASE WHEN ItemNumber = ' + CAST( n AS nvarchar(2)) + N' THEN Item END) AS Diag_' + CAST( n AS nvarchar(2)) + NCHAR(10)

    FROM cteTally

    ORDER BY n

    FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') +

    'FROM #tempTable t

    CROSS APPLY dbo.DelimitedSplitN4K( t.[Diagnoses_For_Spell], '','')s

    GROUP BY t.[Diagnoses_For_Spell];'

    EXEC sp_executesql @SQL;

    References:

    DelimitedSplitN4K: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    You have to love the dynamic crosstab. Nice work, Luis.

  • Ed Wagner (5/20/2016)


    You have to love the dynamic crosstab. Nice work, Luis.

    Thank you, Alan.

    This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (5/20/2016)


    Ed Wagner (5/20/2016)


    You have to love the dynamic crosstab. Nice work, Luis.

    Thank you, Alan.

    This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

    I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.

    BTW, I'm Ed, not Alan. 😉

  • Ed Wagner (5/20/2016)


    Luis Cazares (5/20/2016)


    Ed Wagner (5/20/2016)


    You have to love the dynamic crosstab. Nice work, Luis.

    Thank you, Alan.

    This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

    I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.

    BTW, I'm Ed, not Alan. 😉

    OMG, I saw Alan's name in another thread and got confused. I didn't have enough sleep last night. 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Many thanks for everyone's replies and suggestions. I'm about to leave work now for the weekend so i will have a look at your suggestions over the next couple of days but looking at the responses I think the solutions has been provided.

    As always thanks for your help and time.:-D:-):-D

  • Luis Cazares (5/20/2016)


    Ed Wagner (5/20/2016)


    Luis Cazares (5/20/2016)


    Ed Wagner (5/20/2016)


    You have to love the dynamic crosstab. Nice work, Luis.

    Thank you, Alan.

    This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

    I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.

    BTW, I'm Ed, not Alan. 😉

    OMG, I saw Alan's name in another thread and got confused. I didn't have enough sleep last night. 😀

    No worries, Luis. The sleep thing doesn't get any better.

  • LoosinMaMind (5/20/2016)


    I have the following in a table and need to transfer the single delimited string out into 99 Columns.

    I'm afraid that the 99 Columns is a fixed template but i know from experience of the dataset that we will never reach 99 so I WILL need all 99 Column sin the output but know that they will not all be filled.

    Please find my demo script below along with a sample if the expected output.

    Thanks in advance as always for any help received.

    CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )

    INSERT INTO #temptable

    VALUES

    ( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

    ( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )

    SELECT * FROM #temptable

    DROP TABLE #temptable

    --RESULTS

    Diag_1Diag_2Diag_3Diag_4Diag_5Diag_6Diag_7Diag_8Diag_9Diag_10Diag_11Diag_12Diag_13Diag_14Diag_15Diag_16….To 99

    I64XZ755F03XM819Z755F03XM819Z755

    F03XM819Z755F03XM819Z755F03XM819Z755F03XM819Z755

    E110I10XZ755F03XM819Z755F03XM819Z755

    Just curious and for purposes of performance, John Mitchell is absolutely on the right track... will all elements ALWAYS (except for errors) have exactly 4 characters?

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

  • if these were medical diagnosis codes, i know diagnosis codes vary between three and eight characters in length;

    here's a couple examples

    463

    W45.8XXA

    so i think using a delimitedsplit is going to be required in that case, but i searched a few of the codes, and did not find them in my lookup table....might be qa different "diagnosis" than i was thinking.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • In your data sample you've got only 6 unique diagnosis codes.

    Are you sure you need to repeat them all over and over again in that row you need to populate?

    Is there any value in preserving the order of appearance of those codes in a row?

    _____________
    Code for TallyGenerator

Viewing 14 posts - 1 through 13 (of 13 total)

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