Unpivot Help

  • This should be easy but I'm not getting this to work.

    I have a number of rows that look something like this:

    reckey amt1 amt2amt3 amt4 amt5

    ABCD1234 44.92 14.422 0 11.52

    I'm trying to use the t-sql Unpiovt statement to create this:

    reckey amtnumber amt

    ABCD1234 1 44.92

    ABCD1234 2 14.4

    ABCD1234 3 22

    ABCD1234 4 0

    ABCD1234 5 11.52

    Can anyone provide an example of how to make this work?

  • rburko (9/17/2012)


    This should be easy but I'm not getting this to work.

    I have a number of rows that look something like this:

    reckey amt1 amt2amt3 amt4 amt5

    ABCD1234 44.92 14.422 0 11.52

    I'm trying to use the t-sql Unpiovt statement to create this:

    reckey amtnumber amt

    ABCD1234 1 44.92

    ABCD1234 2 14.4

    ABCD1234 3 22

    ABCD1234 4 0

    ABCD1234 5 11.52

    Can anyone provide an example of how to make this work?

    Other than starting with the link provided by Lynn I would advise you to normalize your data if at all possible. This type of structure is a PITA to work with.

    If you can post ddl, sample data and what you tried so far you will likely find a lot of help. Take a look at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 โ€“ Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • When you've read through that, read this excellent alternative by Dwain Camps[/url].

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (9/17/2012)


    When you've read through that, read this excellent alternative by Dwain Camps[/url].

    But if you are really using SQL 2005, you're going to have to replace the values with the select ... union all select syntax.

  • roryp 96873 (9/17/2012)


    ChrisM@Work (9/17/2012)


    When you've read through that, read this excellent alternative by Dwain Camps[/url].

    But if you are really using SQL 2005, you're going to have to replace the values with the select ... union all select syntax.

    Thanks Rory ๐Ÿ˜€

    SELECT reckey, ProductName, ProductQty

    FROM (SELECT reckey = 'ABCD1234', amt1 = 44.92, amt2 = 14.4, amt3 = 22, amt4 = 0, amt5 = 11.52) d

    CROSS APPLY (

    VALUES

    ('amt1', amt1),

    ('amt2', amt2),

    ('amt3', amt3),

    ('amt4', amt4),

    ('amt5', amt5)

    ) x (ProductName, ProductQty)

    WHERE ProductQty IS NOT NULL

    SELECT reckey, ProductName, ProductQty

    FROM (SELECT reckey = 'ABCD1234', amt1 = 44.92, amt2 = 14.4, amt3 = 22, amt4 = 0, amt5 = 11.52) d

    CROSS APPLY (

    SELECT 'amt1', amt1 UNION ALL

    SELECT 'amt2', amt2 UNION ALL

    SELECT 'amt3', amt3 UNION ALL

    SELECT 'amt4', amt4 UNION ALL

    SELECT 'amt5', amt5

    ) x (ProductName, ProductQty)

    WHERE ProductQty IS NOT NULL

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • --====== TEST DATA SET-UP =================

    if object_id('dbo.pvt') is not null

    drop table dbo.pvt;

    create table dbo.pvt

    (

    reckey varchar(10),

    amt1 decimal(8,2),

    amt2 decimal(8,2),

    amt3 decimal(8,2),

    amt4 decimal(8,2),

    amt5 decimal(8,2)

    );

    insert dbo.pvt values ( 'ABCD1234', 44.92, 14.4, 22, 0, 11.52 );

    insert dbo.pvt values ( 'ABCD5678', 3.12, 17.7, 0, 13, 100.5 );

    /*

    I'm trying to use the t-sql Unpivot statement to create this:

    reckey amtnumber amt

    ABCD1234 1 44.92

    ABCD1234 2 14.4

    ABCD1234 3 22

    ABCD1234 4 0

    ABCD1234 5 11.52

    */

    --====== SOLUTIONS =================

    -- SQL 2005: Unpivot the table.

    SELECT reckey, amtnumber, amt

    FROM

    (SELECT reckey, amt1, amt2, amt3, amt4, amt5

    FROM pvt) p

    UNPIVOT

    (amt FOR amtnumber IN

    (amt1, amt2, amt3, amt4, amt5)

    )AS unpvt;

    -- SQL 2008: You can use Cross Apply:

    SELECT reckey, amtnumber, amt

    FROM dbo.pvt

    CROSS APPLY (

    VALUES (1, amt1)

    ,(2, amt2)

    ,(3, amt3)

    ,(4, amt4)

    ,(5, amt5)

    ) a(amtnumber, amt);

  • ChrisM@Work (9/17/2012)


    Thanks Rory ๐Ÿ˜€

    No problem. I just ran into this problem a week or so ago and after spending way too long searching for a typo in my code, I finally realized I was on a 2k5 box so it's pretty fresh in my memory. :hehe:

  • Thanks for all the replys!

    Lynn, I read the Microsoft documentation but I thought the documentation for pivot was better than it was for Unpivot. If you know of better examples, please let me know.

    Chris, thanks for the link using cross apply.

    Laurie, that's just what I was looking for! I only made a small change for my purposes:

    SELECT reckey, amtnumber, amt

    FROM

    (SELECT reckey, amt1 as [1], amt2 as [2], amt3 as [3], amt4 as [4], amt5 as [5]

    FROM pvt) p

    UNPIVOT

    (amt FOR amtnumber IN

    ([1], [2], [3], [4], [5])

    )AS unpvt;

    Sad to say, I had something similar but I reversed some things (e.g. [1] as amt1, etc.). Thanks for setting me straight.

  • ChrisM@Work (9/17/2012)


    When you've read through that, read this excellent alternative by Dwain Camps[/url].

    I wouldn't exactly call it mine (:blush:) as the initial links in the article attribute it to Brad Shulz and Itzik Ben-Gan (and there are probably others that have written about it before me).

    But thanks for thinking of me.:-D


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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