如何将特定的列数转换为行数

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

注意: GEO_A、GEO_B、GEO_D可能像GEO_ABD一样每天增长30-40列，因此我们需要动态地将GEO_*列转换为行

Answer 1

一种通用方法是使用union all将列取消透视为行：

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    

如果要对结果集进行排序：

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支持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 
;

可以使用dbc.columnsV动态创建IN列表。