Help me form this query - 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)

Help me form this query

Author

Topic

kleinmi
Starting Member

9 Posts

Posted - 2002-08-01 : 11:03:24

(revised previous post)

I have this table:

DATE --- SEVERITY --- BACKLOG
date1 --- 1 -------- 23
date1 --- 2 -------- 34
date1 --- 3 -------- 55
date1 --- 4 -------- 66

Instead 4 rows, I want 1 row with everything combined

ex.

DATE ---- SEV_1 ---- SEV_2 ---- SEV_3--- SEV_4
date 1----23 ------ 34 --------55 ------66

I want to do this is one SELECT Statment

Any ideas on where to start?

Thanks!
Michael

Edited by - kleinmi on 08/01/2002 11:04:28

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-01 : 11:08:49

something ugly like this oughta work...


select
    date,
    (select backlog from kleinmi where date = k.date and severity = 1) as SEV_1,
    (select backlog from kleinmi where date = k.date and severity = 2) as SEV_2,
    (select backlog from kleinmi where date = k.date and severity = 3) as SEV_3,
    (select backlog from kleinmi where date = k.date and severity = 4) as SEV_4,
from
    kleinmi k
group by date

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-01 : 12:19:27

Or:

SELECT date,
Sum(CASE severity WHEN 1 THEN backlog END) as SEV_1,
Sum(CASE severity WHEN 2 THEN backlog END) as SEV_2,
Sum(CASE severity WHEN 3 THEN backlog END) as SEV_3,
Sum(CASE severity WHEN 4 THEN backlog END) as SEV_4,
FROM kleinmi
GROUP BY date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources