How to convert specific number of columns to rows

Question

INPUT        Member GEO_A_0 GEO_A_1 GEO_A_2 GEO_B_0 GEO_B_1 GEO_B_2 GEO_D_0 GEO_D_1 GEO_D_2
               Jon     0       1        0       1       0      0        1       0      0
               Scott   1       0        0       1       0      0        1       0      0
               Suresh  0       0        1       1       0      0        1       0      1

OUTPUT      GEO_A   Jon     0   1   0
            GEO_A   Scott   1   0   0
            GEO_A   Suresh  0   0   1
            GEO_B   Jon     1   0   0
            GEO_B   Scott   1   0   0
            GEO_B   Suresh  1   0   0
            GEO_D   Jon     1   0   0
            GEO_D   Scott   1   0   0
            GEO_D   Suresh  1   0   1

NOTE: GEO_A, GEO_B, GEO_D may grow 30-40 columns everyday like GEO_ABD, so we need to dynamically convert GEO_* columns to rows

Answer 1

A generic approach uses union all to unpivot the columns to rows:

select 'GEO_A' as geo, member, geo_a_0 as col0, geo_a_1 as col1, geo_a_2 as col2 from mytable
union all select 'GEO_B', member, geo_b_0, geo_b_1 as col1, geo_b_2 from mytable
union all select 'GEO_D', member, geo_d_0, geo_d_1 as col1, geo_d_2 from mytable    

If you want to order the resultset:

select *
from (
    select 'GEO_A' as geo, member, geo_a_0 as col0, geo_a_1 as col1, geo_a_2 as col2 from mytable
    union all select 'GEO_B', member, geo_b_0, geo_b_1 as col1, geo_b_2 from mytable
    union all select 'GEO_D', member, geo_d_0, geo_d_1 as col1, geo_d_2 from mytable    
) t
order by geo, member

Answer 2

Teradata supports UNPIVOT:

select *
from vt
unpivot
 ((geo_0,geo_1,geo_2) 
   for x in
  ((GEO_A_0, GEO_A_1, GEO_A_2) as 'GEO_A',
   (GEO_B_0, GEO_B_1, GEO_B_2) as 'GEO_B', 
   (GEO_D_0, GEO_D_1, GEO_D_2) as 'GEO_D')
 ) as pvt 
;

The IN-list could be created dynamically using dbc.columnsV.