transpose Rows to Columns with groups

Database
Enthusiast

transpose Rows to Columns with groups

Hey There,

 

Need Help with this data transpose and sum by groups

 

Partyid    StayNbr  Tower TowerCnt

10             1            A          1

10             1            A          4

10             1           B           3

10             1           B           4

11             1           C           4

....

 

to            

 

Partyid    StayNbr  A      B      C

 

10            1             5       7

11            1                             4

 

I have currently implemented this way...

 

SyntaxEditor Code Snippet

        SELECT PartyId,t.StayNbr,
        sum(case when Tower='A' then Towercnt else 0 end ) as T_A,
        sum(case when Tower='B' then Towercnt else 0 end) as T_B,
        sum(case when Tower='C' then Towercnt else 0 end) as T_C,
        sum(case when Tower='D' then Towercnt else 0 end) as T_D,
        sum(case when Tower='E' then Towercnt else 0 end) as T_E,
        sum(case when Tower='F' then Towercnt else 0 end) as T_F
        FROM TowerAndStay t
        group by PartyId,t.StayNbr  ;

The problem is that the Tower group changes over time and someone has to duplicate a new case when / new column statement.  Is there a way to dynamically create a new field with aggregated values with sql...

 

My environment is Teradata 14.10, if it matters.

 

Please advise?

 

Thanks in advance.

1 REPLY
Junior Contributor

Re: transpose Rows to Columns with groups

The only way to do this automatically is a Stored Procedure creating the Select dynamically based on the result of

SELECT 'case when Tower=''' || Tower || '''then Towercnt else 0 end as T_' || Tower || ','
FROM 
( SELECT DISTINCT Tower
FROM mytable
) AS dt ORDER BY 1

TD16 supports PIVOT, but you still have to know the values in advance.