The PIVOT

  • Comments posted to this topic are about the item The PIVOT

  • Thank you for the post, Steve, interesting one.

    That "*" actually made me easier to select the correct choice. I used the below statement to understand the data and confirm that 3r4c will be the result output. (pivot: change the data representation from vertical to horizontal)

    SELECT

    Customer, Complete, Salesyear, SUM(amount) Amount

    FROM

    (

    SELECT 1 [Customer], 2013[SalesYear], 1 [Complete], 100 [Amount]

    UNION ALL

    SELECT 1,2013,1,200

    UNION ALL

    SELECT 1,2014,0,500

    UNION ALL

    SELECT 1,2014,1,200

    UNION ALL

    SELECT 2,2013,1,200

    UNION ALL

    SELECT 2,2013,1,200

    UNION ALL

    SELECT 2,2014,1,100

    UNION ALL

    SELECT 2,2014,1,100

    ) AS SALES

    GROUP BY Customer, Complete, Salesyear

    ORDER BY 1, 3

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Awesome question, it made me think about PIVOT which I almost never use. Thanks Steve.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!

    with c

    as(

    SELECT * FROM (VALUES

    (1,2013,1,100)

    ,(1,2013,1,200)

    ,(1,2014,0,500)

    ,(1,2014,1,200)

    ,(2,2013,1,200)

    ,(2,2013,1,200)

    ,(2,2014,1,100)

    ,(2,2014,1,100)

    ) AS V([Customer],[Salesyear],[Complete],[Amount])

    )

    SELECT

    *

    FROM c

    PIVOT( SUM(amount)

    FOR Salesyear IN ( [2013], [2014] )

    ) AS pivotsales;

    ;with c

    as(

    SELECT * FROM (VALUES

    (1,2013,1,100)

    ,(1,2013,1,200)

    ,(1,2014,0,500)

    ,(1,2014,1,200)

    ,(2,2013,1,200)

    ,(2,2013,1,200)

    ,(2,2014,1,100)

    ,(2,2014,1,100)

    ) AS V([Customer],[Salesyear],[Complete],[Amount])

    )

    SELECT

    Customer,Complete

    ,sum(case when Salesyear = 2013 then Amount end) as [2013]

    ,sum(case when Salesyear = 2014 then Amount end) as [2014]

    FROM c

    GROUP BY Customer,Complete

  • Thanks Steve for a good question.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Carlo Romagnano (1/27/2015)


    Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!

    ...

    actually I was doing the same thing on v2012, but did not see any difference in the EP, (not sure if I am missing something) but would like to know more... thank you.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal (1/27/2015)


    Carlo Romagnano (1/27/2015)


    Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!

    ...

    actually I was doing the same thing on v2012, but did not see any difference in the EP, (not sure if I am missing something) but would like to know more... thank you.

    See "Estimated Subtree Cost" and "Estimated Number of Rows"

  • nice question, thanks 4 sharing.

  • with sales (Customer, Salesyear, Complete, Amount) as (

    select 1 ,'2013', 1 , 100 union

    select 1 ,'2013', 1 , 200 union

    select 1 ,'2014', 0 , 500 union

    select 1 ,'2014', 1 , 200 union

    select 2 ,'2013', 1 , 200 union

    select 2 ,'2013', 1 , 200 union

    select 2 ,'2014', 1 , 100 union

    select 2 ,'2014', 1 , 100

    )

    SELECT

    *

    FROM sales

    PIVOT( SUM(amount)

    FOR Salesyear IN ( [2013], [2014] )

    ) AS pivotsales;

    Nice question

  • I'm also someone who doesn't use pivot much at all. It seems like I have to look up the syntax nearly every time I use it.

  • Carlo Romagnano (1/27/2015)


    ...See "Estimated Subtree Cost" and "Estimated Number of Rows"

    Thanks, Carlo.

    (Note to self: just seeing EP graphic representation is not enough.. so please dig deeper and see properties of the root iterator.)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Ed Wagner (1/27/2015)


    I'm also someone who doesn't use pivot much at all. It seems like I have to look up the syntax nearly every time I use it.

    In one of my scenarios, customer needed a pivot report, so I wrote a query which fetched the rows (like the sample data given in today's question). The guy who works in the front-end, (I don't know what he did) created report as needed to the client, but the execution time was like nearly 5-6 minutes to display the report depending on the filters provided web-page. So one day I was curious and wanted to learn this PIVOT in the backend level and how it works and modified the same query using PIVOT, the result set of the query was exactly as needed in the report, and his job was to bind the result to the grid. Now the report is displayed in less than 15 seconds. I was like "man this PIVOT feature is really a time saver"

    The tricky part of the pivot is when building a dynamic PIVOT where the columns are not known at the design time but generate the columns at the run time and then concatenate all the strings and execute it.

    Well it was fun... 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Thanks for the question. I have never used PIVOT in my work.

  • Carlo Romagnano (1/27/2015)


    Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!

    with c

    as(

    SELECT * FROM (VALUES

    (1,2013,1,100)

    ,(1,2013,1,200)

    ,(1,2014,0,500)

    ,(1,2014,1,200)

    ,(2,2013,1,200)

    ,(2,2013,1,200)

    ,(2,2014,1,100)

    ,(2,2014,1,100)

    ) AS V([Customer],[Salesyear],[Complete],[Amount])

    )

    SELECT

    *

    FROM c

    PIVOT( SUM(amount)

    FOR Salesyear IN ( [2013], [2014] )

    ) AS pivotsales;

    ;with c

    as(

    SELECT * FROM (VALUES

    (1,2013,1,100)

    ,(1,2013,1,200)

    ,(1,2014,0,500)

    ,(1,2014,1,200)

    ,(2,2013,1,200)

    ,(2,2013,1,200)

    ,(2,2014,1,100)

    ,(2,2014,1,100)

    ) AS V([Customer],[Salesyear],[Complete],[Amount])

    )

    SELECT

    Customer,Complete

    ,sum(case when Salesyear = 2013 then Amount end) as [2013]

    ,sum(case when Salesyear = 2014 then Amount end) as [2014]

    FROM c

    GROUP BY Customer,Complete

    Performance is one of the reasons I use cross tabs instead of PIVOT. I also find the syntax for pivot to be incredibly non-intuitive. The performance differences have been covered extensively by Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

    _______________________________________________________________

    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/

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

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