日期:2014-05-17 浏览次数:20915 次
with t as ( select '2011-08-22' as date_id, 'tag1' as biz_tag from dual union all select '2011-08-22' as date_id, 'tag2' as biz_tag from dual union all select '2011-08-22' as date_id, 'tag3' as biz_tag from dual ) select tab1.date_id, replace(wmsys.wm_concat(case when tab1.biz_tag = 'tag1' then tab1.biz_tag else '' end), ',' , '') as biz_tag1, replace(wmsys.wm_concat(case when tab1.biz_tag = 'tag2' then tab1.biz_tag else '' end), ',' , '') as biz_tag2, replace(wmsys.wm_concat(case when tab1.biz_tag = 'tag3' then tab1.biz_tag else '' end), ',' , '') as biz_tag3 from (select date_id, biz_tag from t group by date_id, biz_tag) tab1 group by tab1.date_id; DATE_ID BIZ_TAG1 BIZ_TAG2 BIZ_TAG3 ---------- --------- --------- -------- 2011-08-22 tag1 tag2 tag3
------解决方案--------------------
----date_id biz_tag1 biz_tag2 biz_tag3 2011-08-22 tag1 tag2 tag3 select date_id, sum(decode(biz_tag,tag1,null)) biz_tag1 , sum(decode(biz_tag,tag2,null)) biz_tag2, sum(decode(biz_tag,tag3,null)) biz_tag3 from tb group by date_id
------解决方案--------------------
3楼写的复杂了啊
with t as ( select '2011-08-22' as date_id, 'tag1' as biz_tag from dual union all select '2011-08-22' as date_id, 'tag2' as biz_tag from dual union all select '2011-08-22' as date_id, 'tag3' as biz_tag from dual ) SELECT date_id, MAX(decode(biz_tag,'tag1','tag1','')) biz_tag1, MAX(decode(biz_tag,'tag2','tag2','')) biz_tag2, MAX(decode(biz_tag,'tag3','tag3','')) biz_tag3 FROM t GROUP BY date_id