self join question - 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)

self join question

Author

Topic

cr488
Starting Member

23 Posts

Posted - 2006-04-06 : 23:31:05

I think I must be braindead today since I could not even firgue this out, so please help me on this. Thanks!

My data set is like this (simple example, actually has more than 5000 records):

table name: data
ID type amt
1 play 50
1 work 100
2 work 150
3 play 0
4 play 100
5 play 20
5 work 30

I am trying to do a self join to display data like this:

ID play_amt work_amt
1 50 100
2 0 15
3 0 0
4 100 0
5 20 30

So that I write my query like this:

select d.id, d.amt as play_amt, d1.amt as work_amt
from data d
left outer join data d1 on d.id = d1.id and d1.type = 'work'
where d.type = 'play'
order by id

I know this query is not working because it is missing some data... Can you help me on this? Thanks a lot!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-06 : 23:36:12

[code]
select ID,
sum(case when type = 'play' then amt else 0 end) as play_amt,
sum(case when type = 'work' then amt else 0 end) as work_amt
from data
group by ID
[/code]

cr488
Starting Member

23 Posts

Posted - 2006-04-07 : 00:58:29

Thanks for the quick reply. This is so simple and clean. I will try it out tomorrow.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources