Moving from data from rows to columns (Top query)

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

SQL Server Development (2000)

Moving from data from rows to columns (Top query)

Author

Topic

hhamill
Starting Member

1 Post

Posted - 2007-01-22 : 16:25:17

Hello,
I'm trying to create a row which contains 5 columns - populated by the results of a query.
Basically, I'm trying to count the instances of something. So I do a "SELECT Top 5 ItemToCount, Count(ItemToCount) as oCount From Table where Client='myclient' Group By ItemToCount ORDER BY oCount DESC".
This will give me a result set like:
Item Count
Item1 140
Item2 110
Item3 20
Item4 10
Item5 1

What I want to end up with is a row like this:

Item1 | Item2 | Item3 | Item4 | Item5
140 | 110 | 20 | 10 | 1

Is there any easy (or maybe not easy) way to do this in SQL?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 17:21:53

[code]
select max(case when ItemToCount = 'Item1' then oCount else 0 end),
max(case when ItemToCount = 'Item2' then oCount else 0 end),
max(case when ItemToCount = 'Item3' then oCount else 0 end),
max(case when ItemToCount = 'Item4' then oCount else 0 end),
max(case when ItemToCount = 'Item5' then oCount else 0 end)
from
(
SELECT Top 5 ItemToCount, Count(ItemToCount) as oCount
From Table
where Client='myclient'
Group By ItemToCount
ORDER BY oCount DESC
) a
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources