--使用decode或case函数,例: with test as( select 3 USER_ID,'背景知识' K_NAME, 4.5 SCORE from dual union all select 3 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all select 3 USER_ID,'服务意识' K_NAME, 1 SCORE from dual union all select 3 USER_ID,'沟通能力' K_NAME, 1 SCORE from dual union all select 4 USER_ID,'背景知识' K_NAME, 12 SCORE from dual union all select 4 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all select 4 USER_ID,'服务意识' K_NAME, 0.5 SCORE from dual union all select 4 USER_ID,'沟通能力' K_NAME, 0.5 SCORE from dual) SELECT USER_ID, SUM(decode(K_NAME, '背景知识', SCORE)) "背景知识", SUM(decode(K_NAME, '创新能力', SCORE)) "创新能力", SUM(decode(K_NAME, '服务意识', SCORE)) "服务意识", SUM(decode(K_NAME, '沟通能力', SCORE)) "沟通能力" FROM test GROUP BY USER_ID;
with test as( select 3 USER_ID,'背景知识' K_NAME, 4.5 SCORE from dual union all select 3 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all select 3 USER_ID,'服务意识' K_NAME, 1 SCORE from dual union all select 3 USER_ID,'沟通能力' K_NAME, 1 SCORE from dual union all select 4 USER_ID,'背景知识' K_NAME, 12 SCORE from dual union all select 4 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all select 4 USER_ID,'服务意识' K_NAME, 0.5 SCORE from dual union all select 4 USER_ID,'沟通能力' K_NAME, 0.5 SCORE from dual) SELECT USER_ID, wm_concat(k_name || '(' || to_char(score,'90.9') || ')') FROM test GROUP BY USER_ID;
列转行 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10));INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL); INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT;SELECT * FROM t_col_row;1)UNION ALL 适用范围:8i,9i,10g及以后版本 SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换,只需加一个where条件, WHERE COLUMN IS NOT NULL 即可。2)MODEL 适用范围:10g及以后 SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION BY (ID) DIMENSION BY (0 AS n) MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3) RULES UPSERT ALL ( cn[1] = 'c1', cn[2] = 'c2', cn[3] = 'c3', cv[1] = c1[0], cv[2] = c2[0], cv[3] = c3[0] ) ORDER BY ID,cn;3)collection 适用范围:8i,9i,10g及以后版本 要创建一个对象和一个集合: CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;SELECT id, t.cn AS cn, t.cv AS cv FROM t_col_row, TABLE(cv_varr(cv_pair('c1', t_col_row.c1), cv_pair('c2', t_col_row.c2), cv_pair('c3', t_col_row.c3))) t ORDER BY 1, 2;
with test as(
select 3 USER_ID,'背景知识' K_NAME, 4.5 SCORE from dual union all
select 3 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 3 USER_ID,'服务意识' K_NAME, 1 SCORE from dual union all
select 3 USER_ID,'沟通能力' K_NAME, 1 SCORE from dual union all
select 4 USER_ID,'背景知识' K_NAME, 12 SCORE from dual union all
select 4 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 4 USER_ID,'服务意识' K_NAME, 0.5 SCORE from dual union all
select 4 USER_ID,'沟通能力' K_NAME, 0.5 SCORE from dual)
SELECT USER_ID,
SUM(decode(K_NAME, '背景知识', SCORE)) "背景知识",
SUM(decode(K_NAME, '创新能力', SCORE)) "创新能力",
SUM(decode(K_NAME, '服务意识', SCORE)) "服务意识",
SUM(decode(K_NAME, '沟通能力', SCORE)) "沟通能力"
FROM test
GROUP BY USER_ID;
with test as(
select 3 USER_ID,'背景知识' K_NAME, 4.5 SCORE from dual union all
select 3 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 3 USER_ID,'服务意识' K_NAME, 1 SCORE from dual union all
select 3 USER_ID,'沟通能力' K_NAME, 1 SCORE from dual union all
select 4 USER_ID,'背景知识' K_NAME, 12 SCORE from dual union all
select 4 USER_ID,'创新能力' K_NAME, 1.5 SCORE from dual union all
select 4 USER_ID,'服务意识' K_NAME, 0.5 SCORE from dual union all
select 4 USER_ID,'沟通能力' K_NAME, 0.5 SCORE from dual)
SELECT USER_ID,
wm_concat(k_name || '(' || to_char(score,'90.9') || ')')
FROM test
GROUP BY USER_ID;
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;SELECT * FROM t_col_row;1)UNION ALL
适用范围:8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换,只需加一个where条件,
WHERE COLUMN IS NOT NULL 即可。2)MODEL
适用范围:10g及以后
SELECT id, cn, cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
PARTITION BY (ID)
DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)
RULES UPSERT ALL
(
cn[1] = 'c1',
cn[2] = 'c2',
cn[3] = 'c3',
cv[1] = c1[0],
cv[2] = c2[0],
cv[3] = c3[0]
)
ORDER BY ID,cn;3)collection
适用范围:8i,9i,10g及以后版本
要创建一个对象和一个集合:
CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;SELECT id, t.cn AS cn, t.cv AS cv
FROM t_col_row,
TABLE(cv_varr(cv_pair('c1', t_col_row.c1),
cv_pair('c2', t_col_row.c2),
cv_pair('c3', t_col_row.c3))) t
ORDER BY 1, 2;
比如表数据:
学科 成绩
语文
英语select decode(学科,'语文','语文') '语文',decode(学科,'英语','英语') '英语' from 表名;