csv unpivot WITH identifying column

  • simply put, I have this in a table:

    username, salesnumbers

    'jack','25,A67,91J'

    and I need

    username, salesnumber

    'jack','25'

    'jack','A67'

    'jack','91J'

    I have found a few examples of just unpivoting the csv values, but none that include the 'key' column.

  • How's this?

    declare @test-2 varchar(100);

    set @test-2 = '''jack'',''25,A67,91J''';

    WITH CTE AS

    (

    SELECT *

    FROM dbo.DelimitedSplit8K(@test, ',')

    )

    SELECT UserName = CTE1.Item, SaleNumber = Replace(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))

    FROM CTE CTE1

    CROSS JOIN (SELECT Item FROM CTE WHERE ItemNumber > 1) CTE2

    WHERE CTE1.ItemNumber = 1;

    Click here for the latest 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

  • Thanks Wayne, your code works wonderfully. I do have a few questions though.

    First, I'm trying to learn/understand this. I get that ItemNumber comes from the DelimitedSplit8K function (which I have and is working). Am I right in guessing that the 'where cte1.ItemNumber =1' is just to select the first row from the returned delimitedsplit8k which is now in a temp table called CTE aliased CTE1. Then a cross join of CTE aliased to CTE2 without the first row so the cross join is doing the work of taking the second and higher values from the CSV aka rows 2+ from the CTE table and gluing the 'Username labeled, row 1 Item field?

    I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join)

    Also, This assumes I have just one row of data to 'unpivot', how can I apply this function to a table of data? (which is about 30 lines long, but will expand to several hundred)?

    example if the question was poorly asked:

    "Username + ',' + csv values" = 'jack,25,A67,91J,35,67,99', so the table is like:

    'jack,25,A67,91J,67,99'

    'jill,5,8,V3'

    'john,7,D4'

    'bill,66,84'

    result:

    'jack','25'

    'jack','A67'

    'jack','91J'

    'jack','67'

    'jack','99'

    'jill','5'

    'jill','8'

    'jill','V3'

    'john','7'

    'john','D4'

    'bill','66'

    'bill','84'

  • Expanding Waynes code:

    DECLARE @test-2 TABLE (id INT, val VARCHAR(100));

    INSERT INTO @test-2

    SELECT 1,'jack,25,A67,91J,67,99' UNION ALL

    SELECT 2,'jill,5,8,V3' UNION ALL

    SELECT 3,'john,7,D4' UNION ALL

    SELECT 4,'bill,66,84';

    WITH CTE AS

    (

    SELECT *

    FROM

    @test-2

    CROSS APPLY

    (SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x

    )

    SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))

    FROM CTE CTE1

    INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id

    WHERE CTE1.ItemNumber = 1;



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • dandenson (1/13/2011)


    Thanks Wayne, your code works wonderfully. I do have a few questions though.

    First, I'm trying to learn/understand this. I get that ItemNumber comes from the DelimitedSplit8K function (which I have and is working). Am I right in guessing that the 'where cte1.ItemNumber =1' is just to select the first row from the returned delimitedsplit8k which is now in a temp table called CTE aliased CTE1. Then a cross join of CTE aliased to CTE2 without the first row so the cross join is doing the work of taking the second and higher values from the CSV aka rows 2+ from the CTE table and gluing the 'Username labeled, row 1 Item field?

    First, I'm glad it's doing what you want... well, almost.

    Yes, the ItemNumber does come from the DelimitedSplit8k function. It is sequential by the position of the data in the delimited string. In this case, the first item in the string is the UserName, so we are looking for the ItemNumber=1.

    You are correct about what the CROSS JOIN is doing.

    I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join)

    A kludge to work with data not properly normalized. ;-):-D

    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

  • LutzM (1/13/2011)


    Expanding Waynes code:

    Thanks Lutz!

    @dandenson - in the first post, you had your expected results encased in single quotes; in your subsequent post you took them out. That whole replace function is because you originally specified it. If you don't want them encased in single-quotes, then change the entire replace function with simply "CTE2.Item".

    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

  • since I already have a table to use, how can I put that in place of

    DECLARE @test-2 TABLE (id INT, val VARCHAR(100));

    INSERT INTO @test-2

    SELECT 1,'jack,25,A67,91J,67,99' UNION ALL

    SELECT 2,'jill,5,8,V3' UNION ALL

    SELECT 3,'john,7,D4' UNION ALL

    SELECT 4,'bill,66,84';

    I tried to just put (select row_number() over (order by name) as id, name + ',' + salesnumbers from thetable;

    but I cant see what to put in place of the @test-2 here? if I put the table name in there but it doesnt work.

    (

    SELECT *

    FROM

    @test-2

    CROSS APPLY

    (SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x

    )

    I did try it with the declare as is just replacing the select lines with my single select and it works, but looks a bit odd.

    otherwise, I am getting the desired results.

    Thanks a lot guys.

  • The temp table has been used to get a code block for demonstration purposes.

    Assuming you're last statement will end with a semicolon, you could simply use

    WITH CTE AS

    (

    SELECT *

    FROM

    YourTableNameGoesHere

    CROSS APPLY

    (SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x

    )

    SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))

    FROM CTE CTE1

    INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id

    WHERE CTE1.ItemNumber = 1; and replace YourTableNameGoesHere with your actual table name.

    If there are any errors, they'd be most like due to different column names. If not, post the full error message andwe'll see if we can assist you any further.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Change:

    (select row_number() over (order by name) as id, name + ',' + salesnumbers from thetable;

    to

    (select row_number() over (order by name) as id,

    val = name + ',' + salesnumbers from thetable;

    Change @test-2 to "thetable"

    that should get you going!

    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

  • the actual query. the Com* columns added together are the key, I add a ',' and attach it to salnum which is the CSV value. If I put the full table path in to the subselect that shows COMS_PAYTABLE below, it errors:

    Msg 207, Level 16, State 1, Line 8

    Invalid column name 'val'.

    this makes sence because the 'val' column is an concatenation. If I replace the select * with select ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val, * I still get the same Invalid column name 'val'. Same if I take the entire select statement from above and put there.

    select row_number() over (order by comgroup) as id,ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val from AWGCustom.dbo.COMS_PAYTABLE;

    WITH CTE AS

    (

    SELECT *

    FROM

    AWGCustom.dbo.COMS_PAYTABLE

    CROSS APPLY

    (SELECT * FROM AWGCustom.dbo.DelimitedSplit8K(val, ',')) x

    )

    SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))

    --into #temp

    FROM CTE CTE1

    INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id

    WHERE CTE1.ItemNumber = 1;

  • I should add that the table AWGCustom.dbo.COMS_PAYTABLE has more fields that I am using in this query. I just dont need them now so I skip them.

Viewing 11 posts - 1 through 10 (of 10 total)

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