有一个记录集:
A 所属公司
1 5 M
2 15 M
3 6 K
4 15 K
5 6 K
6 15 M
7 15 M
8 6 M
9 15 M
10 6 M
11 15 M
12 15 K如何得到A列值相同的记录数?即根据上面的记录集,得到以下记录集:
所属公司 5 15 6
------------------------------
1 M 1 5 2
2 K 0 2 2
A 所属公司
1 5 M
2 15 M
3 6 K
4 15 K
5 6 K
6 15 M
7 15 M
8 6 M
9 15 M
10 6 M
11 15 M
12 15 K如何得到A列值相同的记录数?即根据上面的记录集,得到以下记录集:
所属公司 5 15 6
------------------------------
1 M 1 5 2
2 K 0 2 2
固定列数的行列转换的例子
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下:
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
练习了下代码,借花献佛with tab as(
select 5 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'K' dept from dual
union all
select 15 A,'K' dept from dual
union all
select 6 A,'K' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 15 A,'K' dept from dual
)select dept,sum(decode(A,'5',1,0)) "5",
sum(decode(A,'6',1,0)) "6",
sum(decode(A,'15',1,0)) "15"
from tab
group by dept
CREATE TABLE TABLE_NAME(
a VARCHAR2(10),
b VARCHAR2(10)
);INSERT INTO table_name
select 5 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'K' dept from dual
union all
select 15 A,'K' dept from dual
union all
select 6 A,'K' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 6 A,'M' dept from dual
union all
select 15 A,'M' dept from dual
union all
select 15 A,'K' dept from dual
;
COMMIT;SET serveroutput ON;DECLARE
v_SQL VARCHAR2(32767);
BEGIN
FOR rec IN(SELECT DISTINCT A FROM TABLE_NAME)
LOOP
v_SQL := v_SQL ||' SUM(CASE WHEN T.A = '''||REC.A||''' THEN 1 ELSE 0 END) AS "'||REC.A||'" ,';
END LOOP;
v_SQL := 'SELECT b as 所属公司,' ||v_SQL;
v_SQL := RTRIM(v_SQL,',') ||' FROM TABLE_NAME T GROUP BY T.B' ;
dbms_output.put_line(v_SQL);END;
with t as
(select '5' A, 'M' 所属公司
from dual
union all
select '15', 'M'
from dual
union all
select '6', 'K'
from dual
union all
select '15', 'K'
from dual
union all
select '6', 'K'
from dual
union all
select '15', 'M'
from dual
union all
select '15', 'M'
from dual
union all
select '6', 'M'
from dual
union all
select '15', 'M'
from dual
union all
select '6', 'M'
from dual
union all
select '15', 'M'
from dual
union all
select '15', 'K' from dual)
SELECT 所属公司,
SUM(DECODE(A, 5, 1, 0)) "5",
SUM(DECODE(A, 15, 1, 0)) "15",
SUM(DECODE(A, 6, 1, 0)) "6"
FROM T
GROUP BY 所属公司所属公司 5 15 6
-------- ---------- ---------- ----------
K 0 2 2
M 1 5 2