Pivot views

Question

Pivot views

Ryan1

Ten Centuries

Points: 1019
More actions
September 9, 2013 at 6:52 am

#282160

I am currently reading Itzik Ben Gans book about Microsoft Server 2008 TSQL Fundamentals and I have run into another code example I cannot seem to grasp. The following code runs correctly and selects the data how it should:
Select empid,
SUM(Case when custid = 'a' then qty end) As A,
SUM(Case when custid = 'b' then qty end) AS B,
SUM(Case when custid = 'c' THEN qty end) AS C,
SUM(Case when custid = 'd' THEN qty end) AS D
FROM dbo.orders
GROUP BY empid;
I just don't seem to understand how/why this works?
Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

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

andrea.gnemmi Mr or Mrs. 500 Points: 545 More actions · Answer 1

What is your question precisely? This is the standard solution in order to "pivot" a query in sql, without using the pivot statement. In this solution a Case is used to understand the column value and put in the sum of the corresponding rows. Let me know!

RTaylor2208 SSChampion Points: 13298 More actions · Answer 2

Ryan1 (9/9/2013)
I am currently reading Itzik Ben Gans book about Microsoft Server 2008 TSQL Fundamentals and I have run into another code example I cannot seem to grasp. The following code runs correctly and selects the data how it should:
Select empid,
SUM(Case when custid = 'a' then qty end) As A,
SUM(Case when custid = 'b' then qty end) AS B,
SUM(Case when custid = 'c' THEN qty end) AS C,
SUM(Case when custid = 'd' THEN qty end) AS D
FROM dbo.orders
GROUP BY empid;
I just don't seem to understand how/why this works?
Thank you!

The code will return

1. the the empid as the first column,

2. the aggregated sum of the qty column for all records grouped by empid where the custid = a as a column called A

3. the aggregated sum of the qty column for all records grouped by empid where the custid = b as a column called B

4. the aggregated sum of the qty column for all records grouped by empid where the custid = c as a column called C

5. the aggregated sum of the qty column for all records grouped by empid where the custid = d as a column called D

MCITP SQL 2005, MCSA SQL 2012

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm not sure if this article might help you. It's a great explanation by Jeff Moden

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

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ryan1 Ten Centuries Points: 1019 More actions · Answer 4

Thanks guys.

It makes a lot more sense when I see it written out in English.

I still don't get pivot views entirely. I can write out the code, but seeing it visually in my head and articulating exactly how it works still feels like a very advanced concept. I really appreciate all the help.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Ryan1 (9/10/2013)
Thanks guys.
It makes a lot more sense when I see it written out in English.
I still don't get pivot views entirely. I can write out the code, but seeing it visually in my head and articulating exactly how it works still feels like a very advanced concept. I really appreciate all the help.

Please read the article that Luis has mentioned in his reply......

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ryan1 Ten Centuries Points: 1019 More actions · Answer 6

Ryan1

Ten Centuries

Points: 1019

September 11, 2013 at 9:05 am

#1649365

That is very helpful thank you.