Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

SQL 2000 - Cross Tab Query

Dear Friends,
I have stated this is a cross tab query, but strickly it might not be, so looking for a better description.

I have two columns which contain data which I need to malipuate from a large table.

ColA           ColB
1                  1
1                  2
1                  3
2                  4
2                  5
2                  6

I really need to get a query which will display the data

ColA       ColB     ColB      ColB   ColB
1             1          2           3
2             4          5           6


I really hope this is simple, but I have been stumped for a couple of days now, any help much appreacted.

Craig

 
0
gnidowd
Asked:
gnidowd
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hello,

A cross tab seems to be the proper term, at least it seems like it fits to me if I am understanding correctly.

I think of the result as a pivot on the rank (order) of colb values.

ColA      1st      2nd        3rd   4th
1             1          2           3      NULL
2             4          5           6      NULL

One question there would be do you have a predetermined limit on the number of values you will display like say 5?  If not all rows have 5 that is fine as you will get NULL for those that don't exist.  If unlimited (indeterminate) then have a bigger challenge as you would have to use dynamic SQL or export data to Access or Excel and PIVOT there. :)


For the solution, it would probably be best if you had ability to create views; however, you can get away with a table variable, temp table, or even derived table (just limited here if you want to get fancy as becomes cumbersome if need to query the same table twice -- although the more I think I may show how to do this without self-joining which is my other thought).

Starting here -
Analytical SQL : Where do you rank? - http:A_1555.html
(section "Everyone Ranks The Same!")

You will see how to rank() the values in SQL 2000.  If you have an ID column, you can use that, if not you can rank() by the value of colb if unique.  With the article as explanation, create a view or query of ranked values.

select ColA, ColB
     , (
          select count(*)
          from your_table b
          where b.ColA = a.ColA
          and b.ColB < a.ColA
       ) +1 as ranking
from your_table a
;

Which should get you these results :

ColA           ColB     Ranking
1                  1          1
1                  2          2
1                  3          3
2                  4          1
2                  5          2
2                  6          3


Now you can use a few different techniques, but here is derived table and conditional aggregate approach.

select ColA
     , sum(case ranking when 1 then ColB end) as ColB-1
     , sum(case ranking when 2 then ColB end) as ColB-2
     , sum(case ranking when 3 then ColB end) as ColB-3
     , sum(case ranking when 4 then ColB end) as ColB-4
     , sum(case ranking when 5 then ColB end) as ColB-5
from (

select ColA, ColB
     , (
          select count(*)
          from your_table b
          where b.ColA = a.ColA
          and b.ColB < a.ColA
       ) +1 as ranking
from your_table a

) derived
;


Hope that helps.
0
 
gnidowdAuthor Commented:
mwvisa1,

Awesome, just what the doctor ordered.

Many thanks.

Craig
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome.

Happy coding!

Kevin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now