Query Help - 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

SQL Server Development (2000)

Query Help

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-11-04 : 14:49:28

Hello,
I have a table like this...
ID is the PK in this table

ID Month Category Year
---------------------------------------
1 ---- 1 ----- 1------2003
2 ---- 2 ----- 1------2003
3 ---- 3 ----- 2------2003
4 ---- 3 ----- 1------2003
5 ---- 2 ----- 1------2004
6 ---- 5 ----- 1------2004
7 ---- 6 ----- 2------2004

I want to write a query such that the result should have distinct Category and Year . And for each such row I want to have Mon1,ID1,Mon2,ID2,Mon3,ID3 and so on for all the 12 months. And the values for each Mon1 and ID1 will have value if we have an entry for that month or not. In short the result should look like this...

Category Year Mon1 ID1 Mon2 ID2 Mon3 ID3 ...
-------------------------------------------------------------
1 -------- 2003-----1-----1-----0-----0-----3-----4 ...
2 -------- 2003-----0-----0-----0-----0-----3-----3 ...
1 -------- 2004-----0-----0-----2-----5-----0-----0 ...
2 -------- 2004-----0-----0-----0-----0-----0-----0 ...

I tried doing something like this but it will not give me the way I wanted it.

Select Category,year,
(select Month from Table1 where Table1.ID = T.ID and Month = 1) as Mon1, (select ID from Table1 where Table1.ID = T.ID and Month = 1) as ID1 ,.......so on for all 12 months
from Table1 as T

Thanks for your help,
Maximus

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 15:07:10

What if two ID's have rows for the same month in the same year in the same category? That appears possible since ID is your primary key and not Month/Category/Year (which sounds like it probably SHOULD be your PK).

- Jeff

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-11-04 : 15:23:53

Hi jeff,
You are right. Month/Category/year combination is my PK. And ID is an autonumber and I made that my PK. My application will not allow duplicate (Month/Year/Category) combinations.

Thanks
maximus

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 15:52:56

you can do something like this:


select Category, Year,
   SUM(CASE WHEN Month=1 THEN 1 ELSE 0 END) as Month1,
   SUM(CASE WHEN Month=1 THEN ID ELSE 0 END) as Month1ID,
   SUM(CASE WHEN Month=2 THEN 2 ELSE 0 END) as Month2,
   SUM(CASE WHEN Month=2 THEN ID ELSE 0 END) as Month2ID,
   SUM(CASE WHEN Month=3 THEN 3 ELSE 0 END) as Month3,
   SUM(CASE WHEN Month=3 THEN ID ELSE 0 END) as Month3ID,
   ...etc...
FROM
  YourTable
GROUP BY Category, Year

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-04 : 15:56:04

[code]select
category,
year,
sum(case when month = 1 then 1 else 0 end) as Mon1,
max(case when month = 1 then id else 0 end) as ID1,
sum(case when month = 2 then 2 else 0 end) as Mon2,
max(case when month = 2 then id else 0 end) as ID2,
sum(case when month = 3 then 3 else 0 end) as Mon3,
max(case when month = 3 then id else 0 end) as ID3,
sum(case when month = 4 then 4 else 0 end) as Mon4,
max(case when month = 4 then id else 0 end) as ID4,
sum(case when month = 5 then 5 else 0 end) as Mon5,
max(case when month = 5 then id else 0 end) as ID5
from table1 t
group by category,year
[/code] Jeff, You've got me so gun shy now I am afraid to pull the trigger....

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-11-04 : 17:36:19

Thank you Guys !!!
CASE stmt did it again.

maximus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources