Pivot Table - 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

SQL Server Development (2000)

Pivot Table

Author

Topic

jstrangeway
Starting Member

9 Posts

Posted - 2004-10-21 : 12:59:55

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?

Thanks

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-21 : 17:55:18

Well I can't think of a real simple way but I found a way

Declare @data table(idn int, diag int, recCount int)
Declare @pData table(rank int,idn int, diag int, recCount int)
Declare @idn int, @diag int, @recCount int, @maxID int, @rank int

insert into @data
Select 1, 42, 75 union
Select 1, 49, 50 union
Select 1, 38, 22 union
Select 2, 70, 48 union
Select 2, 33, 27

Declare myValues cursor for
Select   idn,diag, recCount
From     @data
Order by idn, recCount desc

Open myValues
Fetch next from myValues into @idn , @diag , @recCount
Set @maxID = @idn
set @rank = 0
While @@fetch_status = 0
Begin
    set @rank = @rank +1
    Insert into @pData Values(@rank, @idn , @diag , @recCount)            
    Fetch next from myValues into @idn , @diag , @recCount
    if @maxID < @idn
    Begin
        Set @maxID = @idn 
        set @rank = 0
    End
end
close myValues
deallocate myValues

Select * From @pData

Results

rank        idn         diag        recCount    
----------- ----------- ----------- ----------- 
1           1           42          75
2           1           49          50
3           1           38          22
1           2           70          48
2           2           33          27

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

objectuser
Starting Member

14 Posts

Posted - 2004-10-25 : 16:09:28

I would suggest using the row numbering technique that uses a count from here:

http://www.sqlteam.com/item.asp?ItemID=1491

Then all of your rows will be numbered in whatever order you like, and grouped by whatever you like.

One question regarding your data, though: to do a pivot, you'll need to code each column into your select. Do you always have 3 columns, or do you plan to just "have enough" and fill in with nulls?

--Kevin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 16:21:33

this doesn't work for you??

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

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-25 : 16:23:19

Ahhh ! No cursors, please !!!


Declare @data table(idn int, diag int, recCount int)

insert into @data
Select 1, 42, 75 union
Select 1, 49, 50 union
Select 1, 38, 22 union
Select 2, 70, 48 union
Select 2, 33, 27

select a.*,
   (select count(*) 
    from @data b 
    where a.idn = b.idn and a.RecCount <= b.RecCount) as Rank
from 
  @data a

note that your "desired results" are incorrect for idn 2, unless there is something you are not telling us.

- Jeff

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-29 : 11:56:46

quote:
Originally posted by jsmith8858

Ahhh ! No cursors, please !!!
[code]
- Jeff

oh yeah
Well i didn't say it was the best solution

I still have a problem generating simple solutions but I'll get to it some day

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources