Creating a Cross Tab

Database
Enthusiast

Creating a Cross Tab

Essentially what I would like to do is summarize data that I have in a CTE in which a sample of the data is the  following:

TEAM     TYPE   AGE      AGE_BUCKET     ROW_D

RED       CASE   30         1. 0 -30 DAYS      1

BLUE      CASE   32         2. 31 - 60 DAYS    1

GREEN    WATCH  62       3. 61 - 90 DAYS   1

And the table continues on in like manner.   For simplicity I have ommitted columns.  How do I get counts by AGE_BUCKETS for the different teams and scenarios?

So the output looks like this:

TEAM   1. 0 - 30 DAYS    2. 31 - 60 DAYS   3. 61 - 90 DAYS    90 + DAYS         TOTAL

RED             4                     3                       7                          5                    19

GREEN         3                      5                       6                          1                    15

And if I had criteria for the Teams for example count only the team for which TYpe is Case or just Watch then how would I go about doing this Teradata?

Thanks

2 REPLIES
Enthusiast

Re: Creating a Cross Tab

Something like this would do the trick:

SELECT
TEAM,
TYPE,
SUM(CASE WHEN AGE_BUCKET = '1. 0 -30 DAYS' THEN 1 ELSE 0 END) AS "1. 0 -30 DAYS",
SUM(CASE WHEN AGE_BUCKET = '2. 31 - 60 DAYS' THEN 1 ELSE 0 END) AS "2. 31 - 60 DAYS",
SUM(CASE WHEN AGE_BUCKET = '3. 61 - 90 DAYS' THEN 1 ELSE 0 END) AS "3. 61 - 90 DAYS",
SUM(CASE WHEN AGE_BUCKET = '90 + DAYS' THEN 1 ELSE 0 END) AS "90 + DAYS",
COUNT(*) as "TOTAL"
FROM <table>
WHERE TYPE='WATCH'
GROUP BY TEAM,TYPE

You could also do the crosstabbing by the AGE field, which would look somethin g like:

SUM(CASE WHEN AGE BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS "2. 31 - 60 DAYS"
Enthusiast

Re: Creating a Cross Tab

Ah yes. Very nice.  Thanks jnevil!  If I wanted to then summarize the report by saying team red, green and blue are Operations.  Then team black, yellow and orange are Management.  How can I group and replace with these labels?