help with sql pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with sql pivot

Author

Topic

Mark Davies
Starting Member

2 Posts

Posted - 2004-03-08 : 05:33:46

Hi, I have the following query that kinda does what i want

SELECT ABTANumber, TourOperator, ReportStatus,
COUNT(*) AS Counter
FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a
GROUP BY ABTANumber, TourOperator, ReportStatus

This is what it displays

TourOp|ReportStatus|Count
JMC....Fail.........10
JMC....Pass.........621
JMC....Warn.........5
SET....Fail.........12
SET....Pass.........621
SET....Warn.........3

But what i want to display is this

TourOp|Pass|Fail|Warn
JMC....621..10...5
SET....621..12...3

I'm really stuck on this and would appreciate any help
thanks
Mark

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-08 : 08:09:30

Cross tabs will do the trick:

http://www.sqlteam.com/searchresults.asp?SearchTerms=cross+tabs

Mark Davies
Starting Member

2 Posts

Posted - 2004-03-08 : 08:50:22

thanks
got it working like this

SELECT TourOperator,
Pass = SUM(CASE WHEN ReportStatus = 'Pass' THEN 1 ELSE 0 END),
Warn = SUM(CASE WHEN ReportStatus = 'Warn' THEN 1 ELSE 0 END),
Fail = SUM(CASE WHEN ReportStatus = 'Fail' THEN 1 ELSE 0 END)
FROM (Select TourOperator, r.ReportStatus
FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a
GROUP BY TourOperator

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources