Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL Pivot Question Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 5:40 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951
Hello Everyone.

This should be an easy one. It's been a long time since I have used PIVOT and think I forgot something. Take a look at these two queries. The first query provides the desired results. I am trying to get the same results using PIVOT but seem to be missing something.

IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int, qtr tinyint, sales int);

DECLARE @rows int = 10000;
WITH iTally(n) AS
( SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECT n,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;

-- DESIRED RESULTS
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM #p;

-- I AM STUCK HERE
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM #p AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;

Thanks!


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1537999
Posted Tuesday, February 4, 2014 5:51 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 3,937, Visits: 8,927
Hi Alan,
You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM (SELECT qtr, sales FROM #p) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;

Why wouldn't you want to use a nice pre-aggregated cross tab approach?
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM (SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr)p;




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1538002
Posted Tuesday, February 4, 2014 5:54 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951
Luis Cazares (2/4/2014)
Hi Alan,
You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM (SELECT qtr, sales FROM #p) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;

Why wouldn't you want to use a nice pre-aggregated cross tab approach?
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM (SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr)p;



Duh! (slapping self on head!) You rock as always Luis!


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1538003
Posted Tuesday, February 4, 2014 8:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
Luis Cazares (2/4/2014)
Why wouldn't you want to use a nice pre-aggregated cross tab approach?
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM (SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr)p;


+1000


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538024
Posted Wednesday, February 5, 2014 11:31 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951

Luis Cazares (2/4/2014)


... Why wouldn't you want to use a nice pre-aggregated cross tab approach?
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM (SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr)p;



I'm sorry Luis that I missed this question... I was in a hurry to leave work last night, saw that you had replied to my post and did not finish reading it before thanking you (I knew from the first sentence you wrote what I was doing wrong). I finished your post last night.

Thank you Jeff for jumping into the thread, I would have likely missed Luis' question otherwise. As a token of my thanks I sent you some snow.

Ironically, a conversation started last night when I, again, asked my friend/co-worker, Rob, if he read those Jeff Moden Articles I emailed him. Blah, blah... I was showing him why I use the technique in Jeff's article (Cross Tabs and Pivots, Part 1 – Converting Rows to Columns) instead of PIVOT. I was going to walk him through a the diferent techniques (including the pre-aggregations), do some tests, look at query plans but got stuck at the Pivot. We actually had the table #p filled with 1,000,000 rows when I got stuck on PIVOT (I never use PIVOT). I did not post the complete code...

IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int primary key, qtr tinyint, sales int);

DECLARE @rows int = 1000000;
WITH iTally(n) AS
( SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECT n,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;

--DBCC FREEPROCCACHE

SET NOCOUNT ON;
SET STATISTICS TIME ON;
--what I need
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM #p;

--where I was stuck
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM
( SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;
SET STATISTICS TIME OFF;

I have to run (it has not stopped snowing and we're being kicked out). I am going to chime back in later when I'm at home. If you get a moment (I know it's not snowing where you are Luis) run this code above. I have some more questions, such as, "why no parallelism with the cross tab" ? What does the query optimized assume that a serial version of this query will be faster?

Again, I'll chime in again later. Thanks to both of you!


Edit: Grammar.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1538309
Posted Wednesday, February 5, 2014 12:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 3,937, Visits: 8,927
It seems that I get very similar results with pivot and cross tabs. The parallelism is created with the pre-aggregation because the normal pivot and cross tabs won't use it. Both pre-aggregated versions return the exact same plan and similar results every time I ran the test. Maybe I'm missing something. I'll try to come back if I get the time to add an extra column to pivot.
If you have any comments about the test, please let me know.

Code used for the test:
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int primary key, qtr tinyint, sales int);

DECLARE @rows int = 1000000;
WITH iTally(n) AS
( SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECT n,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS

SET NOCOUNT ON;
PRINT 'Normal Cross-tab'
SET STATISTICS TIME ON;
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM #p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Normal Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM
( SELECT qtr, sales
FROM #p) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;
SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Cross-tab'
SET STATISTICS TIME ON;
--where I was stuck
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM (SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr)p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT [1] AS q1, [2] AS q2, [3] AS q3, [4] AS q4
FROM
( SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY qtr) AS p
PIVOT(
SUM(sales)
FOR qtr IN ([1],[2],[3],[4])
) AS pivottbl;
SET STATISTICS TIME OFF;

Results
Normal Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 1350 ms, elapsed time = 1352 ms.


Normal Pivot

SQL Server Execution Times:
CPU time = 1340 ms, elapsed time = 1345 ms.


Pre-aggregated Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 1060 ms, elapsed time = 292 ms.


Pre-aggregated Pivot

SQL Server Execution Times:
CPU time = 1070 ms, elapsed time = 320 ms.


PS. Take care, I hope it doesn't get too terrible with all that snow. I sure miss it but I don't need as much as you might be getting.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1538331
Posted Wednesday, February 5, 2014 1:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 3,937, Visits: 8,927
And this is why I really love cross-tabs. Pivot becomes completely annoying with multiple columns or calculations being pivoted. I struggled to get right the pivot approach.
Again, any comments are welcome.

Code used for the test:
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p(id int primary key, qtr tinyint, sales int);

DECLARE @rows int = 1000000;
WITH iTally(n) AS
( SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT #p
SELECT n,
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS

SET NOCOUNT ON;
PRINT 'Normal Cross-tab'
SET STATISTICS TIME ON;
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4,
COUNT(CASE qtr WHEN 1 THEN sales END) AS Count_q1,
COUNT(CASE qtr WHEN 2 THEN sales END) AS Count_q2,
COUNT(CASE qtr WHEN 3 THEN sales END) AS Count_q3,
COUNT(CASE qtr WHEN 4 THEN sales END) AS Count_q4
FROM #p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Normal Pivot'
SET STATISTICS TIME ON;

SELECT pivottbl.[1] AS q1, pivottbl.[2] AS q2, pivottbl.[3] AS q3, pivottbl.[4] AS q4,
pivottbl2.[1] AS Count_q1, pivottbl2.[2] AS Count_q2, pivottbl2.[3] AS Count_q3, pivottbl2.[4] AS Count_q4
FROM (SELECT qtr, sales FROM #p) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (SELECT qtr, sales FROM #p) AS p2
PIVOT(COUNT(p2.sales) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;

SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Cross-tab'
SET STATISTICS TIME ON;
--where I was stuck
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4,
SUM(CASE qtr WHEN 1 THEN Cnt END) AS Count_q1,
SUM(CASE qtr WHEN 2 THEN Cnt END) AS Count_q2,
SUM(CASE qtr WHEN 3 THEN Cnt END) AS Count_q3,
SUM(CASE qtr WHEN 4 THEN Cnt END) AS Count_q4
FROM (SELECT qtr, SUM(sales) sales, COUNT(*) Cnt
FROM #p
GROUP BY qtr)p;
SET STATISTICS TIME OFF;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
PRINT 'Pre-aggregated Pivot'
SET STATISTICS TIME ON;
--where I was stuck
SELECT pivottbl.[1] AS q1, pivottbl.[2] AS q2, pivottbl.[3] AS q3, pivottbl.[4] AS q4,
pivottbl2.[1] AS Count_q1, pivottbl2.[2] AS Count_q2, pivottbl2.[3] AS Count_q3, pivottbl2.[4] AS Count_q4
FROM (SELECT qtr, SUM(sales) sales FROM #p GROUP BY qtr) AS p
PIVOT(SUM(p.sales) FOR p.qtr IN ([1],[2],[3],[4])) AS pivottbl
JOIN (SELECT qtr, COUNT(*) Cnt FROM #p GROUP BY qtr) AS p2
PIVOT(SUM(Cnt) FOR p2.qtr IN ([1],[2],[3],[4])) AS pivottbl2 ON 1 = 1;
SET STATISTICS TIME OFF;

Results:
Normal Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 2160 ms, elapsed time = 2120 ms.


Normal Pivot

SQL Server Execution Times:
CPU time = 2540 ms, elapsed time = 1741 ms.


Pre-aggregated Cross-tab
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 1160 ms, elapsed time = 475 ms.


Pre-aggregated Pivot

SQL Server Execution Times:
CPU time = 1740 ms, elapsed time = 1271 ms.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1538354
Posted Friday, February 7, 2014 12:38 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951
Luis. Thanks for putting all this together; I wanted to reply soon but it's been a busy couple days.

I have been playing around with the code you posted and re-visited Jeff's article about cross-tabs and pivots. I never read it in it's entirety and never really played with pre-aggregated cross-tabs and pre-aggregated pivots. This was an extremely helpful and informative thread.

I always used the cross-tab approach when pivoting because it was easier to read/write. PIVOT is a real drag when you need to do multiple aggregations; I am now learning that through experience.

What I find interesting is that, on my system (08R2, 64bit, 16gb RAM) & when dealing with one aggregation, the cross-tab approach shows a faster estimated plan but PIVOT performs better. Using the tests below: at a million rows PIVOT generates a parallel plan and is like 4X faster. At 5million they both produce parallel plans and PIVOT is still twice as fast. I did not expect that.

-- (1) Sample data
IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;
CREATE TABLE #p
( id int primary key,
office int not null,
qtr int not null,
sales int not null);
GO

DECLARE @rows int = 2000000;
WITH iTally(n) AS
( SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT #p
SELECT n,
ceiling(60*rand(convert(varbinary, newid()))),
ceiling(4*rand(convert(varbinary, newid()))),
ceiling(80*rand(convert(varbinary, newid())))+20
FROM iTally;
GO

CREATE INDEX c_oqs ON #p(office) INCLUDE (qtr, sales);
CREATE INDEX c_qs ON #p(qtr) INCLUDE (sales);
GO

-- single aggregate: 1m rows=no paralellism for CROSS-TAB, but yes for piv
-- both get paralell plan at 5m rows
---------------------------------------------------------------------------

-- quarters only
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SELECT
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM #p;

WITH p AS
( SELECT qtr, SUM(sales) sales
FROM #p
GROUP BY office, qtr)
SELECT
[1] AS q1,
[2] AS q2,
[3] AS q3,
[4] AS q4
FROM p
PIVOT
( SUM(sales)
FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;
SET STATISTICS TIME OFF;
GO

-- office & quarters only
SET STATISTICS TIME ON;
SELECT
office,
SUM(CASE qtr WHEN 1 THEN sales END) AS q1,
SUM(CASE qtr WHEN 2 THEN sales END) AS q2,
SUM(CASE qtr WHEN 3 THEN sales END) AS q3,
SUM(CASE qtr WHEN 4 THEN sales END) AS q4
FROM #p
GROUP BY office;

--piv
WITH p AS
( SELECT office, qtr, SUM(sales) sales
FROM #p
GROUP BY office, qtr)
SELECT
office,
[1] AS q1,
[2] AS q2,
[3] AS q3,
[4] AS q4
FROM p
PIVOT
( SUM(sales)
FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;

SET STATISTICS TIME OFF;
GO

Results:
5M Rows (both create Parelell Plans)
crossTab


SQL Server Execution Times:
CPU time = 3120 ms, elapsed time = 783 ms.
pivot
SQL Server Execution Times:
CPU time = 1560 ms, elapsed time = 396 ms.

1M Rows (Only PIVOT creates a Parelell Plan)
crossTab
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 946 ms.

pivot
SQL Server Execution Times:
CPU time = 841 ms, elapsed time = 224 ms.

Comments questions welcome.

Thanks again Luis!


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1539354
Posted Friday, February 7, 2014 1:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 3,937, Visits: 8,927
Alan,
I was getting worried about you, it's good to know that you haven't been around because you were busy.
Back to the problem, you're cheating with your test. You didn't clean up the buffer after the cross tabs and before the pivot. At least not in the code you posted. That will give the second method (pivot in this case) a great advantage.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539370
Posted Tuesday, February 11, 2014 4:08 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:17 AM
Points: 632, Visits: 2,951
Luis Cazares (2/7/2014)
...you're cheating with your test. You didn't clean up the buffer after the cross tabs and before the pivot. At least not in the code you posted. That will give the second method (pivot in this case) a great advantage.


Did not know to cleanup the buffer. Learned to do that in this thread. I have been doing so since.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1540485
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse