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

Crosstabs

Author

Topic

supergirl_gem
Starting Member

4 Posts

Posted - 2008-09-01 : 04:35:44

Hi,

I've already got a query which looks as follows:

Select Location, Count(Case When total >= 5 then total end) green, Count(Case When total < 5 and total > - 10 then total end) amber, Count(Case When total <= -10 then total end) red
From
totalValues
Group by Location

this currently outputs my table as below:

Location Red Amber Green
1
2

I want to change this so it's output as follows:

Location 1 Location 2
Red
Amber
Green

If anyone has any suggestions I would appreciate it.

Thanks

Gemma

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-01 : 04:47:58

[code]SELECT Status,
COUNT(CASE WHEN Location = 'Location1' THEN Location END) AS Location1,
COUNT(CASE WHEN Location = 'Location2' THEN Location END) AS Location2
FROM
(
SELECT Location,
CASE WHEN total >= 5 THEN 'Green'
WHEN total < 5 AND total > -10 THEN 'Amber'
WHEN total <= -10 THEN 'Red'
END AS Status
FROM @TABLE
) d
GROUP BY Status[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources