行转列:
SELECT *
FROM src_table
UNPIVOT (param_value FOR param_name IN (product_color AS 'product_color', product_type AS 'product_type', is_intelligent AS 'is_intelligent'));
列转换:
select * from (
select flow,xx from tab
) a pivot (max(value) for enname in ( 'flow' flow ,'xx')) b order by id ";
我自己的测试
注意:建表插入的时候varchar字符型必须要加上'',
insert into s1 values('yuan','english',80);
SELECT * FROM s1
PIVOT ( MAX (score) for subject IN ( 'chinese' chinese , 'math' math , 'english' english ) )
|
注意这里max的是分数,in的是subject
返回来
SELECT * FROM
( SELECT * FROM s1
PIVOT ( MAX(score) for subject IN( 'chinese' chinese , 'math' math , 'english' english ) ) )
UNPIVOT ( score FOR subject IN ( chinese , math , english ) )
注意这里 最后一句的in chinese 之类,chinese不需要加''