今天遇见一个工作问题，表中有218个字段、1600行。要把200列转为行
首先用case when 肯定不行，毕竟有200个字段。其实针对ORACLE11g，有专门的列转行函数 unpivot，附上自己写的一小段Sql语句
一、oracle列转行
SELECT
PRIMPOLLUTE,
CODE_POLLUTE,
POLLUTEVALUE
from STUDENTS--表名
unpivot (POLLUTEVALUE for CODE_POLLUTE in
(W01010_VALUE, W01010_ISSTANDARDS, W01010_STANDARDVALUESTRING, W01001_VALUE, W01001_ISSTANDARDS, W01001_STANDARDVALUESTRING, W01009_VALUE, W01009_ISSTANDARDS, W01009_STANDARDVALUESTRING, W01019_VALUE, W01019_ISSTANDARDS, W01019_STANDARDVALUESTRING, W01018_VALUE, W01018_ISSTANDARDS, W01018_STANDARDVALUESTRING, W01017_VALUE, W01017_ISSTANDARDS, W01017_STANDARDVALUESTRING, W21003_VALUE, W21003_ISSTANDARDS, W21003_STANDARDVALUESTRING, W21011_VALUE, W21011_ISSTANDARDS, W21011_STANDARDVALUESTRING, W21001_VALUE, W21001_ISSTANDARDS, W21001_STANDARDVALUESTRING, W99038_VALUE, W99038_ISSTANDARDS, W99038_STANDARDVALUESTRING, W99039_VALUE, W99039_ISSTANDARDS, W99039_STANDARDVALUESTRING, W21017_VALUE, W21017_ISSTANDARDS, W21017_STANDARDVALUESTRING, W99041_VALUE, W99041_ISSTANDARDS, W99041_STANDARDVALUESTRING, W99042_VALUE, W99042_ISSTANDARDS, W99042_STANDARDVALUESTRING, W99053_VALUE, W99053_ISSTANDARDS, W99053_STANDARDVALUESTRING, W99043_VALUE, W99043_ISSTANDARDS, W99043_STANDARDVALUESTRING, W20117_VALUE, W20117_ISSTANDARDS, W20117_STANDARDVALUESTRING, W99044_VALUE, W99044_ISSTANDARDS, W99044_STANDARDVALUESTRING, W21016_VALUE, W21016_ISSTANDARDS, W21016_STANDARDVALUESTRING, W23002_VALUE, W23002_ISSTANDARDS, W23002_STANDARDVALUESTRING, W22001_VALUE, W22001_ISSTANDARDS, W22001_STANDARDVALUESTRING, W19002_VALUE, W19002_ISSTANDARDS, W19002_STANDARDVALUESTRING, W21019_VALUE, W21019_ISSTANDARDS, W21019_STANDARDVALUESTRING, W02003_VALUE, W02003_ISSTANDARDS, W02003_STANDARDVALUESTRING, W21038_VALUE, W21038_ISSTANDARDS, W21038_STANDARDVALUESTRING, W21022_VALUE, W21022_ISSTANDARDS, W21022_STANDARDVALUESTRING, W21007_VALUE, W21007_ISSTANDARDS, W21007_STANDARDVALUESTRING, W99048_VALUE, W99048_ISSTANDARDS, W99048_STANDARDVALUESTRING, W99049_VALUE, W99049_ISSTANDARDS, W99049_STANDARDVALUESTRING, W24004_VALUE, W24004_ISSTANDARDS, W24004_STANDARDVALUESTRING, W99050_VALUE, W99050_ISSTANDARDS, W99050_STANDARDVALUESTRING, W24009_VALUE, W24009_ISSTANDARDS, W24009_STANDARDVALUESTRING, W24003_VALUE, W24003_ISSTANDARDS, W24003_STANDARDVALUESTRING, W24017_VALUE, W24017_ISSTANDARDS, W24017_STANDARDVALUESTRING, W24024_VALUE, W24024_ISSTANDARDS, W24024_STANDARDVALUESTRING, W24046_VALUE, W24046_ISSTANDARDS, W24046_STANDARDVALUESTRING, W24047_VALUE, W24047_ISSTANDARDS, W24047_STANDARDVALUESTRING, W24048_VALUE, W24048_ISSTANDARDS, W24048_STANDARDVALUESTRING, W24049_VALUE, W24049_ISSTANDARDS, W24049_STANDARDVALUESTRING, W24050_VALUE, W24050_ISSTANDARDS, W24050_STANDARDVALUESTRING, W24062_VALUE, W24062_ISSTANDARDS, W24062_STANDARDVALUESTRING, W24064_VALUE, W24064_ISSTANDARDS, W24064_STANDARDVALUESTRING, W25038_VALUE, W25038_ISSTANDARDS, W25038_STANDARDVALUESTRING, W31001_VALUE, W31001_ISSTANDARDS, W31001_STANDARDVALUESTRING, W31002_VALUE, W31002_ISSTANDARDS, W31002_STANDARDVALUESTRING, W31004_VALUE, W31004_ISSTANDARDS, W31004_STANDARDVALUESTRING, W31003_VALUE, W31003_ISSTANDARDS, W31003_STANDARDVALUESTRING, W25002_VALUE, W25002_ISSTANDARDS, W25002_STANDARDVALUESTRING, W25003_VALUE, W25003_ISSTANDARDS, W25003_STANDARDVALUESTRING, W25004_VALUE, W25004_ISSTANDARDS, W25004_STANDARDVALUESTRING, W99063_VALUE, W99063_ISSTANDARDS, W99063_STANDARDVALUESTRING, W25034_VALUE, W25034_ISSTANDARDS, W25034_STANDARDVALUESTRING, W25010_VALUE, W25010_ISSTANDARDS, W25010_STANDARDVALUESTRING, W25011_VALUE, W25011_ISSTANDARDS, W25011_STANDARDVALUESTRING, W25013_VALUE, W25013_ISSTANDARDS, W25013_STANDARDVALUESTRING, W99052_VALUE, W99052_ISSTANDARDS, W99052_STANDARDVALUESTRING, W99054_VALUE, W99054_ISSTANDARDS, W99054_STANDARDVALUESTRING, W25019_VALUE, W25019_ISSTANDARDS, W25019_STANDARDVALUESTRING, W99055_VALUE, W99055_ISSTANDARDS, W99055_STANDARDVALUESTRING, W99056_VALUE, W99056_ISSTANDARDS, W99056_STANDARDVALUESTRING, W25030_VALUE, W25030_ISSTANDARDS, W25030_STANDARDVALUESTRING, W25032_VALUE, W25032_ISSTANDARDS, W25032_STANDARDVALUESTRING, W99057_VALUE, W99057_ISSTANDARDS, W99057_STANDARDVALUESTRING, W25022_VALUE, W25022_ISSTANDARDS, W25022_STANDARDVALUESTRING, W23020_VALUE, W23020_ISSTANDARDS, W23020_STANDARDVALUESTRING, W23022_VALUE, W23022_ISSTANDARDS, W23022_STANDARDVALUESTRING, W23025_VALUE, W23025_ISSTANDARDS, W23025_STANDARDVALUESTRING, W99058_VALUE, W99058_ISSTANDARDS, W99058_STANDARDVALUESTRING, W26002_VALUE, W26002_ISSTANDARDS, W26002_STANDARDVALUESTRING, W26048_VALUE, W26048_ISSTANDARDS, W26048_STANDARDVALUESTRING, W99010_VALUE, W99010_ISSTANDARDS, W99010_STANDARDVALUESTRING, W99064_VALUE, W99064_ISSTANDARDS, W99064_STANDARDVALUESTRING, W29004_VALUE, W29004_ISSTANDARDS, W29004_STANDARDVALUESTRING, W21009_VALUE, W21009_ISSTANDARDS, W21009_STANDARDVALUESTRING, W20047_VALUE, W20047_ISSTANDARDS, W20047_STANDARDVALUESTRING, W25052_VALUE, W25052_ISSTANDARDS, W25052_STANDARDVALUESTRING, W22007_VALUE, W22007_ISSTANDARDS, W22007_STANDARDVALUESTRING, W23036_VALUE, W23036_ISSTANDARDS, W23036_STANDARDVALUESTRING, W32003_VALUE, W32003_ISSTANDARDS, W32003_STANDARDVALUESTRING, W21023_VALUE, W21023_ISSTANDARDS, W21023_STANDARDVALUESTRING, W33007_VALUE, W33007_ISSTANDARDS, W33007_STANDARDVALUESTRING, W33005_VALUE, W33005_ISSTANDARDS, W33005_STANDARDVALUESTRING, W99003_VALUE, W99003_ISSTANDARDS, W99003_STANDARDVALUESTRING, W33020_VALUE, W33020_ISSTANDARDS, W33020_STANDARDVALUESTRING, W33021_VALUE, W33021_ISSTANDARDS, W33021_STANDARDVALUESTRING, W33022_VALUE, W33022_ISSTANDARDS, W33022_STANDARDVALUESTRING, W33019_VALUE, W33019_ISSTANDARDS, W33019_STANDARDVALUESTRING, W33010_VALUE, W33010_ISSTANDARDS, W33010_STANDARDVALUESTRING, W33011_VALUE, W33011_ISSTANDARDS, W33011_STANDARDVALUESTRING, W33025_VALUE, W33025_ISSTANDARDS, W33025_STANDARDVALUESTRING, W33012_VALUE, W33012_ISSTANDARDS, W33012_STANDARDVALUESTRING, W33047_VALUE, W33047_ISSTANDARDS, W33047_STANDARDVALUESTRING, W33052_VALUE, W33052_ISSTANDARDS, W33052_STANDARDVALUESTRING, W33029_VALUE, W33029_ISSTANDARDS, W33029_STANDARDVALUESTRING, W25043_VALUE, W25043_ISSTANDARDS, W25043_STANDARDVALUESTRING, W20136_VALUE, W20136_ISSTANDARDS, W20136_STANDARDVALUESTRING, W99059_VALUE, W99059_ISSTANDARDS, W99059_STANDARDVALUESTRING, W99004_VALUE, W99004_ISSTANDARDS, W99004_STANDARDVALUESTRING, W21013_VALUE, W21013_ISSTANDARDS, W21013_STANDARDVALUESTRING, W20061_VALUE, W20061_ISSTANDARDS, W20061_STANDARDVALUESTRING, W20038_VALUE, W20038_ISSTANDARDS, W20038_STANDARDVALUESTRING, W99060_VALUE, W99060_ISSTANDARDS, W99060_STANDARDVALUESTRING, W20023_VALUE, W20023_ISSTANDARDS, W20023_STANDARDVALUESTRING, W20004_VALUE, W20004_ISSTANDARDS, W20004_STANDARDVALUESTRING, W99061_VALUE, W99061_ISSTANDARDS, W99061_STANDARDVALUESTRING, W20012_VALUE, W20012_ISSTANDARDS, W20012_STANDARDVALUESTRING, W20101_VALUE, W20101_ISSTANDARDS, W20101_STANDARDVALUESTRING, W20095_VALUE, W20095_ISSTANDARDS, W20095_STANDARDVALUESTRING, W20089_VALUE, W20089_ISSTANDARDS, W20089_STANDARDVALUESTRING))) a
二、DB2列转行
可以先点链接看DB2列转行函数讲解，在看案例
原表：
select * from YWWATER.HLSZNDJCJGTJ where PJJG = '平均值'
原表1
原表2
select aa.*,aa.NF||'-'||aa.YF shijian,b.WATERQUALITYLEVELTARGETNAME,
case when b.PERIOD = 'P' then '平水期' when b.PERIOD = 'F' then '丰水期' when b.PERIOD = 'K' then '枯水期'
else null end PERIOD,
c.REGIONCODE,c.REGIONNAME from
(select
a.NF,
a.YF,
a.HL,
a.DMMC,
a.DMLX ,
a.PJJG,
a.POINTCODE,
a.POINTNAME,
q.polutcode,
q.polut_value
from YWWATER.HLSZNDJCJGTJ_LJ a,
TABLE (VALUES
('Ph(pH无量纲）',a.JCXM_PH),
('溶解氧',a.JCXM_RYL),
('高锰酸盐指数',a.JCXM_GMSYZS),
('生化需氧量（BOD5）',a.JCXM_SHXYL),
('氨氮',a.JCXM_AD),
('石油类（石油醚萃取）',a.JCXM_SYL),
('挥发酚',a.JCXM_HFF),
('汞',a.JCXM_GONG),
('铅',a.JCXM_QIAN),
('镉',a.JCXM_GE),
('阴离子表面活性剂',a.JCXM_YLZBMHXJ),
('铬（六价）',a.JCXM_LUO),
('氟化物（以F-计）',a.JCXM_FHW),
('总磷（以P计）',a.JCXM_ZL),
('氰化物',a.JCXM_QHW),
('硫化物',a.JCXM_LHW),
('砷',a.JCXM_SHEN),
('化学需氧量（CODcr）',a.JCXM_HXXYL),
('铜',a.JCXM_TONG),
('锌',a.JCXM_XIN),
('硒（四价）',a.JCXM_XI)
)
AS q(polutcode, polut_value)) aa
left join (select YEARNUMBER||'-'||MONTHNUMBER||'-'||POINTNAME pointcode,max(WATERQUALITYLEVELTARGETNAME) WATERQUALITYLEVELTARGETNAME,
max(PERIOD) PERIOD
from YWWATER.T_ENV_MANUAL_WATERWQ_MONTH group by YEARNUMBER||'-'||MONTHNUMBER||'-'||POINTNAME ) b on
aa.nf||'-'||aa.YF||'-'||aa.DMMC =b.pointcode
left join TCODE.T_COD_REGION c on substr(aa.POINTCODE,4,4)||'00000000' = c.REGIONCODE
where aa. PJJG = '平均值'
结果
DB2列转行结果
网友评论