oracle 行转列问题

 

 1 select id,
 2        name, 
 3        sum(decode(tag_name, 'AAA', nvl(value, 0),'BBB' ,nvl(value, 0),0)) "imp_value",
 4        sum(decode(tag_name, 'C01', nvl(value, 0),'D01', nvl(value, 0), 0)) "click_value"
 5 
 6   from (SELECT a.id, a.name, d.tag_name, d.value
 7           FROM A a
 8           LEFT JOIN B b
 9             ON a.id = b.id
10           left join d d
11             on b.imp_url = d.tag_name
12         union
13         SELECT a.id, a.name, d.tag_name, d.value
14           FROM A a
15           LEFT JOIN C c
16             ON a.id = c.id
17           left join d d
18             on c.click_url = d.tag_name) m
19  group by id, name
View Code

 

下面是根据id和name 将value合并成一列

 1 select id,
 2        name,
 3        to_char(WMSYS.WM_CONCAT(tag_name)) tag_name,
 4        to_char(WMSYS.WM_CONCAT(value)) value
 5   from (SELECT a.id, a.name, d.tag_name, d.value
 6           FROM A a
 7           LEFT JOIN B b
 8             ON a.id = b.id
 9           left join d d
10             on b.imp_url = d.tag_name
11         union
12         SELECT a.id, a.name, d.tag_name, d.value
13           FROM A a
14           LEFT JOIN C c
15             ON a.id = c.id
16           left join d d
17             on c.click_url = d.tag_name) m
18  group by id, name
View Code


 

posted @ 2013-10-24 09:16  liutoutou  阅读(181)  评论(0)  编辑  收藏  举报