January 26, 2015 at 11:54 pm
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.
January 27, 2015 at 1:03 am
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
January 27, 2015 at 1:37 am
This was removed by the editor as SPAM
January 27, 2015 at 2:28 am
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
January 27, 2015 at 2:55 am
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
🙂
January 27, 2015 at 3:24 am
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.
January 27, 2015 at 4:04 am
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"
January 27, 2015 at 4:36 am
nice question, thanks 4 sharing.
January 27, 2015 at 5:22 am
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
January 27, 2015 at 5:26 am
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.
January 27, 2015 at 5:56 am
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.
January 27, 2015 at 6:23 am
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.
January 27, 2015 at 6:31 am
Thanks for the question. I have never used PIVOT in my work.
January 27, 2015 at 7:16 am
Carlo Romagnano (1/27/2015)
Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!
with cas(
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