Setting up to Allow Cross Tab/Pivot

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

Old Forums

CLOSED - General SQL Server

Setting up to Allow Cross Tab/Pivot

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-22 : 07:31:31

James writes "Hello, I have a question on getting a table to a point that I can pivot it. The following is an example of the data
I have
id diag count
1 42 75
1 49 50
1 38 22
2 70 48
2 33 27

You will notice they are in descending order by count. I need the following I think to be able to cross tab it.
id diag count rank
1 42 75 1
1 49 50 2
1 38 22 3
2 70 48 1
2 33 27 2

How do I get the rank field populated? Is there an easy way to do this? "

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 09:15:27

this should help you:


declare @table table (id int, diag int, count1 int, rank int)
insert into @table
select 1, 42, 75, null union all
select 1, 49, 50, null union all 
select 1, 38, 22, null union all
select 2, 70, 48, null union all
select 2, 33, 27, null union all
select 2, 30, 12, null union all
select 2, 34, 5, null union all
select 2, 54, 3, null union all
select 3, 42, 75, null union all
select 3, 49, 50, null union all 
select 3, 38, 22, null 

declare @cnt int
set @cnt = 0
UPDATE 	t1
SET 	@cnt = rank = 	case when 
			exists (select top 1 id from @table where id<t1.id) and
			not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1)
			then 1
			else @cnt + 1
			end
from @table t1

select * from @table

Go with the flow & have fun! Else fight the flow

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 08:54:22

Hello, This works great except for one problem I did not anticipate. If the first two counts or all 3 counts are exactly the same it ranks them the same. Example the following will happen.
id diag count rank
1 , 10 , 1 , 1
1 , ab , 1 , 1
1 , cr , 1 , 1

would like to see the rank be 1,2,3 in this case.

Any suggestions?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-29 : 09:18:56

really? this works fine for me...


declare @table table (id int, diag int, count1 int, rank int)
insert into @table
select 1, 42, 1, null union all
select 1, 49, 1, null union all 
select 1, 38, 1, null union all
select 1, 33, 3, null union all
select 1, 15, 3, null union all

select 2, 70, 48, null union all
select 2, 33, 27, null union all
select 2, 30, 12, null union all
select 2, 34, 5, null union all
select 2, 54, 3, null union all
select 3, 42, 75, null union all
select 3, 49, 50, null union all 
select 3, 38, 22, null 

declare @cnt int
set @cnt = 0
UPDATE 	t1
SET 	@cnt = rank = 	case when 
			exists (select top 1 id from @table where id<t1.id) and
			not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1)
			then 1
			else @cnt + 1
			end
from @table t1

select * from @table

maybe you should be more specific. use the data in this example to show us the error??

Go with the flow & have fun! Else fight the flow

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 09:49:08

I am not sure. but for id = 1 I got the following.
id,diag,count1,rank
1,42,1,1
1,49,1,2
1,38,1,3
1,33,3,4
1,15,3,5

I would expect it to be flipped and rank 1 and 2 have the count of 3 and rank 3,4,5 have the count of 1.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-29 : 09:54:30

emm... why??

maybe it would be good if you described the correct logic behind this. it sure would be easier...

Go with the flow & have fun! Else fight the flow

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 10:11:08

I'm sorry. I guess that would help.

I have a maximum of 3 per id based on count (descending). which I am then wanting to rank those top 3 (or 2 or 1 if they only had 1 diag) by count descending. the top diagnosis's for a recipient. so if recipeint 1 had been diagnosed with a cold 25 times, the flu 10 times and step throat 1.

It would be ranked 1,2,3 for that recipeint. If a recipient had 3 diagnosis but all were only 1 count. I would expect it to be 1,2,3 with no importance of which was ranked 1,2 or 3.

The specific problems is as follows
Select 06300961, 03, 1, null union all
select 06300961, 02, 1, null union all
select 06300961, 4, 1, null

These rank 1,1,1

Thanks

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 10:21:30

Also, does it matter that the id field is a character?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-29 : 10:27:45

no it doesn't matter. i'll take a look at it a bit later because i don't have time right now...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-29 : 10:41:07

see my example of how to calculate a "Rank" column, and how to handle ties, here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28976

You can simply apply that logic to your situation.

- Jeff

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 12:58:44

Thanks Jeff but I am having trouble applying it. I have up to 3 of the same id and want to rank based on an int field with numbers

Thanks

jstrangeway
Starting Member

9 Posts

Posted - 2004-12-29 : 13:51:54

Thanks Jeff, Here is my result that works using your logic
declare @table table (id int, diag int, count1 int, rank int)
insert into @table
select 1, 42, 1, null union all
select 1, 49, 1, null union all
select 1, 38, 1, null union all
select 1, 33, 3, null union all
select 1, 15, 3, null union all

select 2, 70, 48, null union all
select 2, 33, 27, null union all
select 2, 30, 12, null union all
select 2, 34, 5, null union all
select 2, 54, 3, null union all
select 3, 42, 75, null union all
select 3, 49, 50, null union all
select 3, 38, 22, null union all
Select 06300961, 03, 1, null union all
select 06300961, 02, 1, null union all
select 06300961, 4, 1, null

select *,
(select count(*)
from
@table s2
where
s1.id = s2.id and
(s2.count1 > s1.count1 OR
(s2.count1 = S1.count1 and
S2.diag <= S1.diag))) as Rank
FROM
@table s1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources