为简化说明应用,假设转置前,数据库中表有三列:日期,国家,数量。如下表:
国家 | 日期 | 数量 |
B | 2005-8-8 | 40.00 |
B | 2005-8-7 | 6.00 |
C | 2005-8-8 | 77.00 |
A | 2005-8-9 | 33.00 |
A | 2005-8-8 | 9.00 |
C | 2005-8-7 | 21.00 |
要求转置为列标题为日期,国家,并按合计数量从左到右排列国家名称。
如下表:
日期 | C | B | A |
2005-8-7 | 21 | 6 | 0 |
2005-8-8 | 77 | 40 | 9 |
2005-8-9 | 0 | 0 | 33 |
合计 | 98 | 46 | 42 |
思路:
先在装置前,将国家按合计数量降序排列。形如:
国家 | 日期 | 数量 | 合计 |
C | 2005-8-7 | 21.00 | 98.00 |
C | 2005-8-8 | 77.00 | 98.00 |
B | 2005-8-8 | 40.00 | 46.00 |
B | 2005-8-7 | 6.00 | 46.00 |
A | 2005-8-9 | 33.00 | 42.00 |
A | 2005-8-8 | 9.00 | 42.00 |
再进行动态转置。装置的基本思路是拼SQL,
第一部分是,SELECT CALLDATE,
第二部分是,sum(case country=’A’ THEN count else 0 end ) as ‘A’ 的国家字段
第三部分是 from 之后的语句。
关键在于不要将第二部分的case语句写死,按数据库中国家的出现的顺序来拼这个 case语句。
用如下SQL形成一个SQL的中间表:
内容为:
,sum(case when COUNTRY=C then CallCount else 0 end) "C" |
,sum(case when COUNTRY=B then CallCount else 0 end) "B" |
,sum(case when COUNTRY=A then CallCount else 0 end) "A" |
将该中间表的内容逐行读出,拼为SQL即可。
附:生成动态case语句的SQL:
SELECT ',sum(case when COUNTRY='
|| COUNTRY
|| ' then CallCount else 0 end)'
|| ' "'
|| COUNTRY
|| '"' c2
FROM (
select a.*,b.heji
from before1 a
left join
(
select country, sum(count) as heji
from before1
group by country
) b
on a.country=b.country
order by heji desc
)
GROUP BY COUNTRY;
附准备数据SQL :
CREATE TABLE before1(
country VARCHAR2(2 BYTE),
calldate DATE,
count INTEGER
);
INSERT INTO before1( country, calldate, count)
VALUES ('B', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);
NSERT INTO before1( country, calldate, count)
VALUES ('B', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);
INSERT INTO before1( country, calldate, count)
VALUES ('C', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);
INSERT INTO before1( country, calldate, count)
VALUES ('A', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);
INSERT INTO before1( country, calldate, count)
VALUES ('A', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);
INSERT INTO before1( country, calldate, count)
VALUES ('C', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);