SELECT TYPE, MAX(DECODE(NAME,'单位1',VALUE) as 单位1, MAX(DECODE(NAME,'单位2',VALUE) as 单位2, MAX(DECODE(NAME,'单位3',VALUE) as 单位3, …… …… FROM( select name,'受理数' as type,受理数 AS VALUE from T UNION ALL select name,'办结数' as type,办结数 AS VALUE from T UNION ALL select name,'超期数' as type,超期数 AS VALUE from T) GROUP BY TYPE
可以使用语句生成上述查询语句,但是除了xml中可以使用any关键字,其他地方只能把所有值都枚举出来 执行下面语句生成查询语句,将生成的查询语句拿去执行就是了 select 'SELECT TYPE,'|| WMSYS.WM_CONCAT('MAX(DECODE(NAME,'''||NAME||''',VALUE) as 单位'||ROWNUM) ||' FROM( select name,''受理数'' as type,受理数 AS VALUE from T UNION ALL select name,''办结数'' as type,办结数 AS VALUE from T UNION ALL select name,''超期数'' as type,超期数 AS VALUE from T) GROUP BY TYPE' from T
MAX(DECODE(NAME,'单位1',VALUE) as 单位1,
MAX(DECODE(NAME,'单位2',VALUE) as 单位2,
MAX(DECODE(NAME,'单位3',VALUE) as 单位3,
……
……
FROM(
select name,'受理数' as type,受理数 AS VALUE from T
UNION ALL
select name,'办结数' as type,办结数 AS VALUE from T
UNION ALL
select name,'超期数' as type,超期数 AS VALUE from T)
GROUP BY TYPE
参考 Oracle 11g 行列互换 pivot 和 unpivot 说明
执行下面语句生成查询语句,将生成的查询语句拿去执行就是了
select
'SELECT TYPE,'||
WMSYS.WM_CONCAT('MAX(DECODE(NAME,'''||NAME||''',VALUE) as 单位'||ROWNUM)
||'
FROM(
select name,''受理数'' as type,受理数 AS VALUE from T
UNION ALL
select name,''办结数'' as type,办结数 AS VALUE from T
UNION ALL
select name,''超期数'' as type,超期数 AS VALUE from T)
GROUP BY TYPE' from T