SQL 2000 - Cross Tab Query

Posted on 2009-10-15
Last Modified: 2012-05-08
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.


Question by:gnidowd
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 60

Accepted Solution

Kevin Cross earned 500 total points
ID: 25582018

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.

Author Closing Comment

ID: 31641631

Awesome, just what the doctor ordered.

Many thanks.

LVL 60

Expert Comment

by:Kevin Cross
ID: 25582916
You are most welcome.

Happy coding!


Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question