views:

29

answers:

1

I have the following table with data:

tag    dt          value
1      2009/03/01  10.2
2      2009/03/01  12
3      2009/03/01  120
....
1      2009/03/02  13.2
2      2009/03/02  9
3      2009/03/02  23
....

Basically, for a group of tags, there are values along date (March 1, 2, ...). I would like to have data in a different view like this:

dt             tag(1)   tag(2)   tag(3)
2009/03/01     10.2     12       120
2009/03/02     13.2     9        23
....

I tried to use the following SQL to get only tag(1)' value

SELECT dt, SUM(value) [tag(1)] FROM myTable
GROUP BY dt, tagid
HAVING tagid = 1

how can I get all tag(1), tag(2) and tag(3)'s values in one column by date by using TSQL?

By the way I have Microsoft SQL Server 2005.

+3  A: 
SELECT dt, 
SUM(case when tag=1 then value else 0 end) as tag1,
SUM(case when tag=2 then value else 0 end) as tag2,
SUM(case when tag=3 then value else 0 end) as tag3
FROM myTable
GROUP BY dt
Arvo
nice with case statement!
David.Chu.ca