T-SQL Pivot with a WHERE clause?

[gbaughma]

I'm attempting to write a Pivot table (I have some reports that I am currently doing pivot tables with ASP code, and it's messy)... but I'm having an issue with putting in a WHERE clause.

Here's an example.....

SELECT Project.ProjectID, Location.LocationName, 
[Sample].CollectionDate, 
Analyte.AnalyteName, 
Results.SignField, 
Results.Result
FROM Location INNER JOIN ((
Analyte INNER JOIN 
Analysis 
ON Analyte.AnalyteID = Analysis.AnalyteID) INNER JOIN (((
Project INNER JOIN 
Timeslips 
ON Project.ProjectID = Timeslips.ProjectID) INNER JOIN (
COC INNER JOIN (
[Sample] INNER JOIN 
SeriesSample 
ON [Sample].SampleID = SeriesSample.SampleID) 
ON COC.COCID = [Sample].COCID) 
ON Timeslips.TimeslipsID = COC.TimeslipsID) INNER JOIN 
Results 
ON SeriesSample.SeriesSampleID = Results.SeriesSampleID) 
ON Analysis.AnalysisID = Results.AnalysisID) 
ON Location.LocationID = [Sample].LocationID 
PIVOT (
SUM (Results.Result)
FOR AnalyteName IN ([Benzene],[Toluene],[Ethyl Benzene],[Xylenes],[MTBE],[GRO],[DRO])
)
WHERE Project.ProjectID=15

... it does NOT like that "Where" clause... but I only need results for that one project ID.... (or, a selected project ID).....

Any thoughts on the syntax for adding a WHERE to a pivot?


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[bborissov]

Put it before PIVOT keyword.
And make yourself a favor always put joining condition AFTER the JOIN clause.
The way you use it make the query almost unreadable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gbaughma]

Meh. Forgot the "AS" Clause.....

Now, my next obstacle is that "Results.result" is, in fact, a STRING instead of a NUMBER...

It doesn't need to be summed... there should be only the one result... is there another function that I can use that will work with strings?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Grrrrrrrrrrr....

I'm getting:
Msg 488, Level 16, State 1, Line 26
Pivot grouping columns must be comparable. The type of column "CoverLetterTemplate" is "ntext", which is not comparable.

OK, I'm not even SELECTING the "CoverLetterTemplate" field. What the heck??????



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

It looks like I can use MIN or MAX instead of SUM, which will handle the text (instead of numeric) values of "Result".

I'm still getting the above error though, on a field I'm not even selecting.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

If you are using the pivot operator, you must be using sql 2005 or greater, right?

If you are using sql2005 or greater, you shouldn't have any ntext columns in any table. You should convert all of your text columns to varchar(max) and all your ntext columns to nvarchar(max).

C'mon..... what are you waiting for? Do it now!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Mmm.... unfortunately (as seen in my other posts) it's not that simple.

This is the "linked to an access backend database" situation, and changing the field types isn't really an option.

The good news is, this system is going to be replaced within the next 6 months. The bad news is, it will be replaced with another Access database (ARRRGH!) But... I think I've managed to convince the developer to at least put the tables that *I* need in SQL.

<Sigh>


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

... of course, it still doesn't explain why it's looking for a field that I'm not even SELECTing....


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com