Crosstab, or something like it

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)

Crosstab, or something like it

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-29 : 17:13:19

I'm struggling with a very slow, inefficient query, and I can't get my head around the right way to do this.

I've got a very large table with relationships between users. Easy enough. Now, for a given source user, I want to get a list of the target users and rel_types that apply. That't the part that's driving me mad.

create table user_rels(i_users_source int,i_users_target int,rel_type tinyint)

insert into user_rels(i_users_source,i_users_target,rel_type)
select 100,200,1
union all
select 100,200,2
union all
select 100,300,1
union all
select 100,400,1
union all
select 100,500,4


The output I'm looking for (when looking up relationships for source user 100) is:i_users_target,rel_1,rel_2,rel_3,rel_4
200,1,1,0,0
300,1,0,0,0
400,1,0,0,0
500,0,0,0,1


Right now, I'm doing something terrible: I'm using a table variable, inserting a list of all target users that the source user has a relationship with, and then (shudders):[code]

select i_users,login_name,
  IsNull((select rel_value from user_rels where rel_type=1 and i_users_source=@iUsers and i_users_target=i_users),0) as rel_1,
  IsNull((select rel_value from user_rels where rel_type=2 and i_users_source=@iUsers and i_users_target=i_users),0) as rel_2,
  IsNull((select rel_value from user_rels where rel_type=3 and i_users_source=@iUsers and i_users_target=i_users),0) as rel_3,
  IsNull((select rel_value from user_rels where rel_type=4 and i_users_source=@iUsers and i_users_target=i_users),0) as rel_4
from @tUsers
order by i_users asc

...as you can see, that gets more and more expensive with the addition of each relationship type.

There's got to be a better way, and I suspect it involves crosstabs. But I've been mucking with it all morning with no luck. Help!

Thanks
-b

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-12-29 : 18:18:58

How's this?


DECLARE @SourceUser INT

SELECT @SourceUser = 100

create table #user_rels(i_users_source int,i_users_target int,rel_type tinyint)

insert into #user_rels(i_users_source,i_users_target,rel_type)
select 100,200,1
union all
select 100,200,2
union all
select 100,300,1
union all
select 100,400,1
union all
select 100,500,4


select u.i_users_target,
	MAX(CASE u.rel_type WHEN 1 THEN 1 ELSE 0 END) AS rel_1,
	MAX(CASE u.rel_type WHEN 2 THEN 1 ELSE 0 END) AS rel_2,
	MAX(CASE u.rel_type WHEN 3 THEN 1 ELSE 0 END) AS rel_3,
	MAX(CASE u.rel_type WHEN 4 THEN 1 ELSE 0 END) AS rel_4
from #user_rels u
WHERE u.i_users_source = @SourceUser
GROUP BY i_users_target

drop table #user_rels

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-29 : 21:19:27

You are my hero. I knew there was a simpler way, but I just couldn't figure how to use aggregates like that.

Thanks!
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources