WITH test AS(
SELECT '20140101' work_date,'AA' status,'B201' grid,'037' device FROM dual UNION ALL
SELECT '20140103' work_date,'CC' status,'B201' grid,'037' device FROM dual UNION ALL
SELECT '20140301' work_date,'AA' status,'B201' grid,'057' device FROM dual UNION ALL
SELECT '20140306' work_date,'CC' status,'B201' grid,'057' device FROM dual UNION ALL
SELECT '20140308' work_date,'AA' status,'B202' grid,'055' device FROM dual)
SELECT grid,
MAX(decode(status || device,'AA037',work_date,NULL)) AA037,
MAX(decode(status || device,'CC037',work_date,NULL)) CC037,
MAX(decode(status || device,'AA057',work_date,NULL)) AA057,
MAX(decode(status || device,'CC057',work_date,NULL)) CC057,
MAX(decode(status || device,'AA055',work_date,NULL)) AA055
FROM test
GROUP BY grid
给你写个例子吧。。需要注意两个事情。。
1.首先你的列要可以列举出来,这个sql做不到动态
2. 字段的命名不能以数字开头。。所以037/AA是不能作为列名的。。
SELECT '20140101' work_date,'AA' status,'B201' grid,'037' device FROM dual UNION ALL
SELECT '20140103' work_date,'CC' status,'B201' grid,'037' device FROM dual UNION ALL
SELECT '20140301' work_date,'AA' status,'B201' grid,'057' device FROM dual UNION ALL
SELECT '20140306' work_date,'CC' status,'B201' grid,'057' device FROM dual UNION ALL
SELECT '20140308' work_date,'AA' status,'B202' grid,'055' device FROM dual)
SELECT grid,
MAX(decode(status || device,'AA037',work_date,NULL)) AA037,
MAX(decode(status || device,'CC037',work_date,NULL)) CC037,
MAX(decode(status || device,'AA057',work_date,NULL)) AA057,
MAX(decode(status || device,'CC057',work_date,NULL)) CC057,
MAX(decode(status || device,'AA055',work_date,NULL)) AA055
FROM test
GROUP BY grid
给你写个例子吧。。需要注意两个事情。。
1.首先你的列要可以列举出来,这个sql做不到动态
2. 字段的命名不能以数字开头。。所以037/AA是不能作为列名的。。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货