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.
| Author |
Topic |
|
thari
Starting Member
1 Post |
 Posted - 2007-01-24 : 21:02:03
|
| Hi,I have two tables Category and items. I have 5 categoried stored in the category table. Item table has (item id, item name and categeory id)I have to display item name, item count in each category. If an item does does not belong to a category, the count should be zero. it should look like this...ItemName Cat1 Cat2 Cat3 Cat4 Cat5aaa 2 1 0 0 1bbb 0 0 3 1 4If the count is zero means, the item table has no values for the category.Any suggestion ...will be help fullWarm Regards,Thiyaga |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-24 : 21:05:02
|
[code]select i.ItemName, Cat1 = count(case when category_id = 'Cat1' then 1 end), Cat2 = count(case when category_id = 'Cat2' then 1 end), Cat3 = count(case when category_id = 'Cat3' then 1 end), Cat4 = count(case when category_id = 'Cat4' then 1 end), Cat5 = count(case when category_id = 'Cat5' then 1 end)from Item igroup by i.ItemName[/code]
KH |
 |
|
|
|
|
|
table comparison