All Forums Database
CCSlice 36 posts Joined 03/14
19 Mar 2014
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
 
 

jnevill 17 posts Joined 03/12
20 Mar 2014

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"

 

CCSlice 36 posts Joined 03/14
20 Mar 2014

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?

You must sign in to leave a comment.