DB2 Crosstab Procedure 1

[jcale4]

I am migrating data from Access to DB2. I am embedding my queries in my ASP pages and everything seems to be going well, however i am having problems finding a suitable feature in DB2 that will simulate an Access crosstab query. I believe a stored procedure will be my only option. This is OK, but i'm not very familiar with stored procedures. Here is an example of what i need:

I have a very simple table that looks like this (derived from a view):

CATEGORY SUMCAT SUBCATEGORY SUMSUB NAME DATERATING
-------- ------ ----------- ------ ---- ----------
APPROACH 2 ERROR 1 JOE 2005-12-12
LOGICAL 3 SUGGST. 3 JOHN 2005-11-11

I want to present that data like this:

Category / Subcategory NOV DEC
---------------------- --- ---
APPROACH 2
LOGICAL 3
ERROR 1
SUGGST 3

The date ranges will be defined in the ASP page and should be passed into the function that builds the crosstab.

Can anyone help me with this?

Thank you!

 

[blom0344]

I really do not know if you could do this with a DB2 stored procedure. However, by using CASE you should be able to store data in buckets like a crosstab query would do:

[blue]
SELECT
CATEGORY AS 'Category/Subcategory',
CASE(WHEN DATERATING BETWEEN '2005-11-01' AND '2005-11-30' THEN SUMCAT ELSE 0 END) AS 'NOV',
CASE(WHEN DATERATING BETWEEN '2005-12-01' AND '2005-12-31' THEN SUMCAT ELSE 0 END) AS 'DEC'
FROM TABLE 
UNION
SUBCATEGORY,
CASE(WHEN DATERATING BETWEEN '2005-11-01' AND '2005-11-30' THEN SUMSUB ELSE 0 END) AS 'NOV',
CASE(WHEN DATERATING BETWEEN '2005-12-01' AND '2005-12-31' THEN SUMSUB ELSE 0 END) AS 'DEC'
FROM TABLE

[/blue]

However,this is not a dynamical solution. You need to know in advance how many columns you need for the output in monthly buckets....






Ties Blom
Information analyst

 

[jcale4]

Perfect! I will work on developing a stored procedure to possibly incorporate dynamic capabilities.

Thank you!

 

[Whitt1]

In regards to your post on Crosstab; can you post the stored procedure when you get it built. I would be very interested in how this works.
We are using DB2 v7 z/OS and someone asked me about Crosstabl and if we could do it in this environment. I have no idea where to start. I have their SQL but thats about it.

Thanks: