Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

Question

Post reply

Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

Greg Larsen

SSC-Insane

Points: 20966
More actions
May 27, 2015 at 12:00 am

#145672

Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator
Gregory A. Larsen, MVP

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

akljfhnlaflkj

SSC Guru

Points: 76202

May 27, 2015 at 8:05 am

#1800922

Thanks for the PIVOT training.

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 2

I appreciate that this is an example for an exam, and learning how to PIVOT (and UNPIVOT) is important.

I haven't redone the performance testing recently, but from memory there was a performance issue with pivot which meant that you were actually better off doing a CASE statement to get the same values. Is that still valid, or have they fixed PIVOT?

So your query becomes something like this:

DECLARE

@StartDate DATE

, @EndDate DATE

SET @StartDate = '2005-01-01'

SET @EndDate = '2006-12-31'

SELECT

YEAR(OrderDate) AS OrderYear

, TerritoryID

, SUM(CASE WHEN MONTH(OrderDate) BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS [1]

, SUM(CASE WHEN MONTH(OrderDate) BETWEEN 4 AND 6 THEN 1 ELSE 0 END) AS [2]

, SUM(CASE WHEN MONTH(OrderDate) BETWEEN 7 AND 9 THEN 1 ELSE 0 END) AS [3]

, SUM(CASE WHEN MONTH(OrderDate) BETWEEN 10 AND 12 THEN 1 ELSE 0 END) AS [4]

FROM Sales.SalesOrderHeader

WHERE OrderDate >= @StartDate

AND OrderDate <= @EndDate

GROUP BY YEAR(OrderDate), TerritoryId

ORDER BY YEAR(OrderDate), TerritoryId

valeryk2000 SSCarpal Tunnel Points: 4245 More actions · Answer 3

Toby: I've the same experience - SUM(Case ... works faster than PIVOT

paolo.bert SSC Enthusiast Points: 152 More actions · Answer 4

I am not a PRO, but I used them in Access97, writing SQL code (TRANSFORM command), a long time ago, and as far as I remember there wasn't that burden of work to have dynamic columns... Is there any advantage in SQL Server?

kbenton 6603 Old Hand Points: 374 More actions · Answer 5

Thanks for the excellent tutorial. I've had to create numerous pivot tables over the years and it was a struggle learning them initially. You've laid out easy to follow examples and bonus...included the dynamic pivoting. It's terrific having both examples explained so well together in one article.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

paolo.bert (9/23/2016)
I am not a PRO, but I used them in Access97, writing SQL code (TRANSFORM command), a long time ago, and as far as I remember there wasn't that burden of work to have dynamic columns... Is there any advantage in SQL Server?

The PIVOT operator in Access blows the doors off the PIVOT operator in SQL Server in just about every aspect. I was amazed, disappointed, and frustrated at how crippled and relatively difficult it is to use compared to what is in Access. Why Microsoft didn't steal their own code to make it as robust as that found in Access, I'll never know.

There's a relatively "ancient" technique of pivoting data that seems easier (to me, anyway) to understand, doesn't have the performance problems of the PIVOT operator, and is more flexible when it comes to things like adding line-end totals, adding "bottom line" and sub totals, displaying a "0" instead of a NULL for elements with no data, building multiple-pivoted columns (ex. Qty and $ amounts in the same report) and in building dynamic "rolling date" reports. It's called a CROSS TAB. Don't look for it in any of the newer versions of "Books Online" because MS removed it.

CROSS TAB isn't an operator in SQL Server. It's the name of a technique that's becoming a lost art and proof of the old adage that "Change is inevitable... change for the better is not". Combined with "Pre-Aggregation" techniques (thank you Peter "Peso" Larsson for the term), CROSS TABs can be roughly twice as fast as PIVOTs even if you apply the same pre-aggregation techniques to a PIVOT operator.

For an introduction to the CROSS TAB technique and pre-aggregation and includes performance testing (although a bit dated now), please see the following article.

http://www.sqlservercentral.com/articles/T-SQL/63681/

For an introduction in how to make automatic "rolling date" pivots using dynamic SQL along with the CROSS TAB technique, please see the following article.

http://www.sqlservercentral.com/articles/Crosstab/65048/

Bottom line for me is that I don't use the PIVOT operator in SQL Server.

Still, even the CROSS TAB technique pales in comparison to the PIVOT operator found in ACCESS.

--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)

paolo.bert SSC Enthusiast Points: 152 More actions · Answer 7

What to say, Jeff... First of all, maaany thanks for your thorough reply. I was afraid to post a so 'stupid' question on this Guru's cave, but now I am confortable to see that I am not the only one who find this implementation quite strange... Thanks again! 🙂

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

paolo.bert (9/23/2016)
What to say, Jeff... First of all, maaany thanks for your thorough reply. I was afraid to post a so 'stupid' question on this Guru's cave, but now I am confortable to see that I am not the only one who find this implementation quite strange... Thanks again! 🙂

My pleasure and thank you for the feedback, Paolo.

--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)

h.tobisch SSCommitted Points: 1671 More actions · Answer 9

I do not like the wording. A column HAS a name and CONTAINS values.

so, to my understanding,

'first pivoted column' should be 'first value of pivoted column'

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

h.tobisch (9/23/2016)
I do not like the wording. A column HAS a name and CONTAINS values.
so, to my understanding,
'first pivoted column' should be 'first value of pivoted column'

Since the author is talking about a column and not just the first value in the column, I have to disagree with your disagreement.

--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)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 11

Jeff Moden (9/23/2016)

paolo.bert (9/23/2016)
For an introduction to the CROSS TAB technique and pre-aggregation and includes performance testing (although a bit dated now), please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
For an introduction in how to make automatic "rolling date" pivots using dynamic SQL along with the CROSS TAB technique, please see the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/

Thanks for the tips and links.

h.tobisch SSCommitted Points: 1671 More actions · Answer 12

well, whatever:

pivot column or column being pivoted refers (to my understanding, which you are invited to correct)

to the original column containing the values, whereas the columns resulting from the values in that column

might be referred to by ,say, 'pivot result column'

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

Jeff Moden (9/23/2016)
h.tobisch (9/23/2016)
I do not like the wording. A column HAS a name and CONTAINS values.
so, to my understanding,
'first pivoted column' should be 'first value of pivoted column'
Since the author is talking about a column and not just the first value in the column, I have to disagree with your disagreement.

My pleasure. Thank you for the feedback.

--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)

paolo.bert SSC Enthusiast Points: 152 More actions · Answer 14

paolo.bert

SSC Enthusiast

Points: 152

September 27, 2016 at 11:17 am

#1903423

Thanks to All!