SQL-列转行

1
2
3
4

create table t1 as 
select '1' as id ,'mike' as name ,'22' as age,'0' as gender
union all      
select '2' as id ,'kangkang' as name ,'19' as age,'1' as gender

1
2
3
4
5
6
7
8
9

select id,'name' as type,name as value
from t1
union all
select id,'age' as type,age as value
from t1
union all
select id,'gender' as type,gender as value
from t1
order by id

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

select 
    id, 
    type, 
    value
from 
(
    select 
        t.id, 
        t1.pos1, 
        t1.value1 as value,
        t2.pos2, 
        t2.type2  as type
    from 
    (
        select 
            id, 
            concat_ws(',', name, age, gender) as value, 
            array('name', 'age', 'gender') as type
        from t1
    ) t
    lateral view posexplode(split(t.value, ',')) t1 as pos1, value1 
    lateral view posexplode(t.type) t2 as pos2, type2
) t
where t.pos1 = t.pos2

1
2
3
4
5
6
7
8

select 
    id, 
    b.type2 as type, 
    a.value1 as value
from t1
    lateral view posexplode(split(concat_ws(',', t1.name, t1.age, t1.gender), ',')) a as pos1, value1
    lateral view posexplode(array('name', 'age', 'gender')) b as pos2, type2
where a.pos1 = b.pos2

1
2
3
4
5
6
7
8
9

select 
    id, 
    type, 
    case type
        when 'name' then name
        when 'age' then age
        when 'gender' then gender
        else null end as value
from t1 lateral view explode(array('name', 'age', 'gender')) t2 as type