Using Case Within PIVOT portion of Crosstab query

Question

Post reply

Using Case Within PIVOT portion of Crosstab query

skilly2

Right there with Babe

Points: 761
More actions
April 13, 2015 at 11:54 am

#295976

I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0
I'm using this statement to create the result set:
SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription
FROM (tlbTestType INNER JOIN (tblProductTestClassification INNER JOIN
jctProductClassificationRequiredTest ON tblProductTestClassification.ProductTestClassID
=jctProductClassificationRequiredTest.ProductTestClassID) ON
tlbTestType.TestID=jctProductClassificationRequiredTest.TestID)
INNER JOIN tblInventory ON tblProductTestClassification.ProductTestClassID
=tblInventory.ProductTestClassID) PT
PIVOT
(COUNT (ProductTestClassID) FOR TestDescription IN
(Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],Melamine,Moisture,
Mold, Salmonella, [Vomitoxin (DON)], Yeast)) AS TestRequirements
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 X X X X X
1024 Item1024 X X X X X
135 Item135 X X X X X
107 Item107 X X X
106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
If needed, I'd be more than happy to create DDL statements to create test data and sample data, I just figured someone would be able to look at the statement and tell me relatively easily how to accomplish this.

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

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

Y.B. SSChampion Points: 11642 More actions · Answer 1

Try using the CASE statements in the top level SELECT.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Stop using Pivot altogether. Just use a CROSSTAB, which is driven by CASE statements to begin with. See the following article for a primer.

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

As a bit of a sidebar, the proper use and indentation of some decent table aliases along with a standardized indentation convention would make your code a whole lot easier to read.

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

skilly2 Right there with Babe Points: 761 More actions · Answer 3

yb751 (4/13/2015)
Try using the CASE statements in the top level SELECT.

This is what I ended up doing. I just wasn't sure what aggregate to use for the PIVOT portion by doing it this way, but I ended up using MIN instead of COUNT and it worked great. Thanks for the push in the right direction.

skilly2 Right there with Babe Points: 761 More actions · Answer 4

Jeff Moden (4/13/2015)
Stop using Pivot altogether. Just use a CROSSTAB, which is driven by CASE statements to begin with. See the following article for a primer.
http://www.sqlservercentral.com/articles/T-SQL/63681/
As a bit of a sidebar, the proper use and indentation of some decent table aliases along with a standardized indentation convention would make your code a whole lot easier to read.

This is the first CROSSTAB I've done in SQL. When I came across how to do it, I saw that CASE statements were the solution before PIVOT came along in SQL Server 2005. I just assumed it was a better way to do it. I will give the CASE option a try the next time I come across the need for the CROSSTAB.

As for the indentation, I wasn't quite sure how to go about it because the code windows seem to always only be a certain width when I post. I figured it was easier to read without the proper indentation then having to scroll constantly to view the code. Is there a way to change the width of the code windows when you post? I have read the article in you signature, and try to follow that. Even in that article, the code windows are wider. Not sure why that is. I also searched for an easy way to format the results... let me tell you that was no easy task to line everything up in the correct column. Is there an easy way to do that when posting?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

skilly2 (4/14/2015)
Jeff Moden (4/13/2015)
Stop using Pivot altogether. Just use a CROSSTAB, which is driven by CASE statements to begin with. See the following article for a primer.
http://www.sqlservercentral.com/articles/T-SQL/63681/
As a bit of a sidebar, the proper use and indentation of some decent table aliases along with a standardized indentation convention would make your code a whole lot easier to read.
This is the first CROSSTAB I've done in SQL. When I came across how to do it, I saw that CASE statements were the solution before PIVOT came along in SQL Server 2005. I just assumed it was a better way to do it. I will give the CASE option a try the next time I come across the need for the CROSSTAB.
As for the indentation, I wasn't quite sure how to go about it because the code windows seem to always only be a certain width when I post. I figured it was easier to read without the proper indentation then having to scroll constantly to view the code. Is there a way to change the width of the code windows when you post? I have read the article in you signature, and try to follow that. Even in that article, the code windows are wider. Not sure why that is. I also searched for an easy way to format the results... let me tell you that was no easy task to line everything up in the correct column. Is there an easy way to do that when posting?

Ah. Understood.

When I write code in SSMS, I have it set up (under {Tools}{Options}) to convert tabs to spaces with tab stops set at 4. That way, it doesn't matter where you post... if the leading spaces are observed (like they are in the code windows), your code will always look the same no matter what the forum tab stops are set at. Even with the spacing, tabs will still work as expected when you're writing the code.

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Just to demonstrate how much difference aliases can make. I used aliases in your joins and that massive wall of text in the middle is a LOT more friendly.

SELECT ItemKey

, Description

, Aflatoxin

, [Coliform Bacteria]

, [E_Coli],[Fumonisin]

, Melamine

, Moisture

, Mold

, Salmonella

, [Vomitoxin (DON)]

, Yeast

FROM

(

SELECT tblInventory.ItemKey

, tblInventory.Description

, pcrt.ProductTestClassID

, tt.TestDescription

FROM tlbTestType tt

INNER JOIN jctProductClassificationRequiredTest pcrt ON tt.TestID = pcrt.TestID

INNER JOIN tblProductTestClassification ptc ON ptc.ProductTestClassID = pcrt.ProductTestClassID

INNER JOIN tblInventory i ON ptc.ProductTestClassID = i.ProductTestClassID

) PT

PIVOT (COUNT (ProductTestClassID) FOR TestDescription IN

(

Aflatoxin

, [Coliform Bacteria]

, [E_Coli]

, [Fumonisin]

, Melamine

, Moisture

, Mold

, Salmonella

, [Vomitoxin (DON)]

, Yeast

)

) AS TestRequirements

_______________________________________________________________

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/