Post reply

A Possible Alternative to SQL UNPIVOT

  • January 15, 2017 at 10:13 pm

    #325468

    Comments posted to this topic are about the item A Possible Alternative to SQL UNPIVOT

  • January 15, 2017 at 11:06 pm

    #1922873

    Thank you very much. Very informative indeed.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • January 16, 2017 at 4:43 am

    #1922942

    Hi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.

    A. Mauricio Repetto
    ML Engineer

  • January 16, 2017 at 5:23 am

    #1922947

    amd.repetto - Monday, January 16, 2017 4:43 AM

    Hi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.

    Hi amd.repetto, I am curious to see a quick example of the OUTER APPLY + VALUES way of doing unpivot.

  • January 16, 2017 at 5:42 am

    #1922951

    Interesting technique Paul. Well done.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • January 16, 2017 at 6:16 am

    #1922958

    R M Buda - Monday, January 16, 2017 5:23 AM

    amd.repetto - Monday, January 16, 2017 4:43 AM

    Hi! Thanks for the alternatives 🙂 but I think you've missed the OUTER APPLY + VALUES option. it may be the simplest way of doing an unpivot.

    Hi amd.repetto, I am curious to see a quick example of the OUTER APPLY + VALUES way of doing unpivot.

    Here you have Buda 🙂 I always use this approach for unpivoting data: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    A. Mauricio Repetto
    ML Engineer

  • January 16, 2017 at 4:16 pm

    #1923064

    paul_milligan01 - Sunday, January 15, 2017 10:12 PM

    Comments posted to this topic are about the item A Possible Alternative to SQL UNPIVOT

    I'm terrible with XML and wouldn't presume to try to convert your code from using a single row to unpivot to unpivoting a table.  That being said, would you post some code that will unpivot the following table please?  The example return that I'm looking for can be found in the code after the test table creation code.

    --===== If the test table already exists, drop it to make reruns in SSMS easier.
         IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
            DROP TABLE #TestTable
    ;
    --===== Create a test table with a PK in place
     CREATE TABLE #TestTable
            (
             RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
            ,ColA   INT
            ,ColB   INT
            ,ColC   INT
            )
    ;
    --===== Populate the table using minimal logging if not in FULL Recovery Model
     INSERT INTO #TestTable WITH (TABLOCK)
            (    ColA, ColB, ColC)
     SELECT TOP 100000
             ColA   = ABS(CHECKSUM(NEWID())%1000)+1
            ,ColB   = ABS(CHECKSUM(NEWID())%1000)+1
            ,ColC   = ABS(CHECKSUM(NEWID())%1000)+1
       FROM      sys.all_columns ac1
      CROSS JOIN sys.all_columns ac2
    ;


    For those interested in how to unpivot a multi-row table, you do need some form of unique column, which is RowNum in the example table above.  It doesn't have to be either the PK or a Clustered Index or an IDENTITY column but it does need to be unique.

    From there, it's a nearly trivial exercise as seen in the following code:

    --===== Un-Pivot the columns using a CROSS APPLY and VALUES.
     SELECT tt.RowNum, ca.ColName, ca.ColValue
       FROM #TestTable tt
      CROSS APPLY   (VALUES 
                         ('ColA',ColA)
                        ,('ColB',ColB)
                        ,('ColC',ColC)
                    ) ca (ColName,ColValue)
    ;

    Also, I realize that the aggregations of the data isn't the primary focus of this article but you might want to add a WHERE clause so the COUNT in the following results from near the end of the article comes up with the correct answer.
      StatisticName       ProductCount
    ------------------- ------------
    ProductsWithNoPrice 3
    Products500OrLess   4
    ProductsOver1500    6
    TotalCount          14

    --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, 2017 at 6:38 pm

    #1923311

    Not sure that the author of this article can post back so can anyone else answer my question at the top of the previous post?  I'd like to do a bit of a performance test.  You never know where you might find a golden nugget.

    --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 18, 2017 at 12:09 pm

    #1923519

    I do like the flow.  One thing though - your intro is defining how to *pivot* (how to turn 3 rows into columns), NOT how to un-pivot.  A bit silly - but it can be a little confusing if you're not familiar with the concept since your technique section then describe turning the columns back into rows (which is the actual unpivot part).<b

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 18, 2017 at 1:04 pm

    #1923531

    Jeff Moden - Monday, January 16, 2017 4:16 PM

    For those interested in how to unpivot a multi-row table, you do need some form of unique column, which is RowNum in the example table above.  It doesn't have to be either the PK or a Clustered Index or an IDENTITY column but it does need to be unique.

    You do not need a unique column in order to unpivot.  This can easily be seen by just removing the RowNum column from your test table and results.  The reason to have a unique column is to re-pivot once you have unpivoted.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 18, 2017 at 5:31 pm

    #1923581

    drew.allen - Wednesday, January 18, 2017 1:04 PM

    Jeff Moden - Monday, January 16, 2017 4:16 PM

    For those interested in how to unpivot a multi-row table, you do need some form of unique column, which is RowNum in the example table above.  It doesn't have to be either the PK or a Clustered Index or an IDENTITY column but it does need to be unique.

    You do not need a unique column in order to unpivot.  This can easily be seen by just removing the RowNum column from your test table and results.  The reason to have a unique column is to re-pivot once you have unpivoted.

    Drew

    Yep... understood.  That's why you actually do need a unique column to unpivot. 😉  Makes little sense, otherwise.

    --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 18, 2017 at 5:34 pm

    #1923582

    Just to ask the question again, would someone show me how to apply this XML method of unpivoting to a whole table instead of just one row?  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)

  • January 19, 2017 at 7:07 am

    #1923695

    Jeff Moden - Tuesday, January 17, 2017 6:38 PM

    Not sure that the author of this article can post back so can anyone else answer my question at the top of the previous post?  I'd like to do a bit of a performance test.  You never know where you might find a golden nugget.

    If you want to use the XML technique to unpivot only a subset of columns, you need to convert the columns you want to unpivot into an XML column, leaving the other (identifying) columns as normal. Then the XML functions can do their magic.

    Here is your example:


    --===== If the test table already exists, drop it to make reruns in SSMS easier.
      IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
       DROP TABLE #TestTable
    ;
    --===== Create a test table with a PK in place
    CREATE TABLE #TestTable
       (
       RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
       ,ColA INT
       ,ColB INT
       ,ColC INT
       )
    ;
    --===== Populate the table using minimal logging if not in FULL Recovery Model
    INSERT INTO #TestTable WITH (TABLOCK)
       (ColA, ColB, ColC)
    SELECT TOP 100000
       ColA = ABS(CHECKSUM(NEWID())%1000)+1
       ,ColB = ABS(CHECKSUM(NEWID())%1000)+1
       ,ColC = ABS(CHECKSUM(NEWID())%1000)+1
     FROM  sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
    ;
    ;
    WITH TableWithXmlCol AS
    (
    SELECT RowNum,
    (SELECT
    ColA,
    ColB,
    ColC
    FROM #TestTable P
    WHERE P2.RowNum = P.RowNum
    FOR XML AUTO, ELEMENTS, TYPE
    ) AS XmlCol
    FROM #TestTable P2
    )
    SELECT
    TableWithXmlCol.RowNum,
      ColName = T.c.value('fn:local-name(.)', 'sysname'),
      ColVal = T.c.value('.', 'varchar(20)')
    FROM TableWithXmlCol
    CROSS APPLY TableWithXmlCol.XmlCol.nodes('//P/*') T(c)

  • January 19, 2017 at 8:20 am

    #1923719

    Excellent.  Thank you.  Now for some testing! 😉

    --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 28, 2017 at 2:56 am

    #1925473

    Very interesting article. In my opinion there could be a little problem in case you deal with a query containing one or more joins. In this case the resultant XML is not as flat as the one of the example. Look at the following code, for example:

    CREATE TABLE A1 (Id INT, Agent varchar(20))
    CREATE TABLE R1 (AgentId INT, Revenue2014 INT,  Revenue2015 INT, Revenue2016 INT)

    INSERT INTO A1 VALUES
    (1, 'John'),
    (2, 'Paul'),
    (3, 'Alex')
    INSERT INTO R1 VALUES
    (1, 100, 200, 250),
    (2, 120, 80, 95),
    (3, 50, 70, 150)

    SELECT Agent, Revenue2014, Revenue2015, Revenue2016
    FROM A1
    INNER JOIN R1 ON A1.Id = R1.AgentId
    FOR XML AUTO, ELEMENTS, TYPE

    The XML would be something like that, and the parsing operation would be more difficult.

    <A1>
      <Agent>John</Agent>
      <R1>
        <Revenue2014>100</Revenue2014>
        <Revenue2015>200</Revenue2015>
        <Revenue2016>250</Revenue2016>
      </R1>
    </A1>
    <A1>
      <Agent>Paul</Agent>
      <R1>
        <Revenue2014>120</Revenue2014>
        <Revenue2015>80</Revenue2015>
        <Revenue2016>95</Revenue2016>
      </R1>
    </A1>
    <A1>
      <Agent>Alex</Agent>
      <R1>
        <Revenue2014>50</Revenue2014>
        <Revenue2015>70</Revenue2015>
        <Revenue2016>150</Revenue2016>
      </R1>
    </A1>
    The use of a CTE does not help. One possible workaround is to put the result of the query into a temporary table and then select from it.
    Any other ideas?

Viewing 15 posts - 1 through 15 (of 18 total)

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