SQL行列转换

1.SQL行转列
建表:

create table public.row_change_column
(name varchar(100),
course varchar(100),
score int) ;

插入数据 :

insert into public.row_change_column
      select 'zhangsan','语文',82
union select 'zhangsan','数学',95
union select 'zhangsan','英语',80
union select 'lisi','语文',77
union select 'lisi','数学',90
union select 'lisi','英语',81
;

原始数据格式 :

行转列： 主要是通过case when 来对行进行判断

select 
name,
max(case course when '语文' then score else 0 end) as '语文',
max(case course when '数学' then score else 0 end) as '数学',
max(case course when '英语' then score else 0 end) as '英语'
from public.row_change_column
group by name

或者 :

select 
name,
max(case when course='语文' then score else 0 end) as '语文',
max(case when course='数学' then score else 0 end) as '数学',
max(case when course='英语' then score else 0 end) as '英语'
from public.row_change_column
group by name

2.列转行

建表 :

create table public.column_change_row (
product varchar(100),
btconn int ,
wificonn int ,
gpsconn int 
);

插入数据 :

insert into public.column_change_row
      select '大米',55,90,98
union select '一星',42,78,90
union select 'ov',67,88,97
;

原始数据格式 :

列转行 : 主要是通过union

select 
PRODUCT,
'BT' AS TYPE ,
btconn AS KPI
FROM public.column_change_row
UNION ALL 
select 
PRODUCT,
'WIFI' AS TYPE ,
wificonn AS KPI
FROM public.column_change_row
UNION ALL 
select 
PRODUCT,
'GPS' AS TYPE ,
gpsconn AS KPI
FROM public.column_change_row
ORDER BY PRODUCT,TYPE
;