1) 列转行
WITH TB AS
(
SELECT '1' AS SID,
'英语' AS E,
'70' AS E_SCORE,
'数学' S,
'89' AS S_SCORE,
'语文' AS T,
'69' AS T_SCORE
FROM DUAL
)
SELECT SID,E AS 科目,E_SCORE AS 分数
FROM
(
SELECT SID,E,E_SCORE FROM TB
UNION ALL
SELECT SID,S,S_SCORE FROM TB
UNION ALL
SELECT SID,T,T_SCORE FROM TB
);
2) 行转列 (数值类型)
WITH TB AS
(
SELECT '1' AS SID, '英语' AS E,'70' AS E_SCORE FROM DUAL
UNION ALL
SELECT '1' AS SID, '数学' S,'89' AS S_SCORE FROM DUAL
UNION ALL
SELECT '1' AS SID, '语文' AS T,'69' AS T_SCORE FROM DUAL
UNION ALL
SELECT '2' AS SID, '英语' AS E,'72' AS E_SCORE FROM DUAL
UNION ALL
SELECT '2' AS SID, '数学' S,'58' AS S_SCORE FROM DUAL
UNION ALL
SELECT '2' AS SID, '语文' AS T,'60' AS T_SCORE FROM DUAL
UNION ALL
SELECT '3' AS SID, '英语' AS E,'85' AS E_SCORE FROM DUAL
UNION ALL
SELECT '3' AS SID, '数学' S,'90' AS S_SCORE FROM DUAL
)
SELECT SID,
SUM(DECODE(E,'英语',E_SCORE)) AS 英语,
SUM(DECODE(E,'语文',E_SCORE)) AS 语文,
SUM(DECODE(E,'数学',E_SCORE)) AS 数学
FROM TB
GROUP BY SID;
3) 方法一:行转列 (字符串类型,适用于数值类型)
WITH TB AS
(
SELECT 1 AS ID,'你' AS NAME FROM DUAL
UNION ALL
SELECT 1 ,'好' FROM DUAL
UNION ALL
SELECT 1 ,'吗' FROM DUAL
UNION ALL
SELECT 2 ,'我' FROM DUAL
UNION ALL
SELECT 2 ,'不' FROM DUAL
UNION ALL
SELECT 2 ,'好' FROM DUAL
),
TB2 AS
(
SELECT ID,
NAME,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
FROM TB
),
TB3 AS
(
SELECT ID,
MAX(SYS_CONNECT_BY_PATH(NAME,' ')) AS NAME
FROM TB2
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1 AND PRIOR ID = ID
GROUP BY ID
)
SELECT ID,REPLACE(NAME,' ','') AS NAME
FROM TB3;
4) 方法二:行转列 (字符串类型,适用于数值类型)
WITH TB AS
(
SELECT 1 AS ID,'你' AS NAME FROM DUAL
UNION ALL
SELECT 1 ,'好' FROM DUAL
UNION ALL
SELECT 1 ,'吗' FROM DUAL
UNION ALL
SELECT 2 ,'我' FROM DUAL
UNION ALL
SELECT 2 ,'不' FROM DUAL
UNION ALL
SELECT 2 ,'好' FROM DUAL
),
TB2 AS
(
SELECT ID,
NAME,
RN,
LAG(RN) OVER(PARTITION BY ID ORDER BY RN) AS LG
FROM (
SELECT ID,NAME,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
FROM TB
)
),
TB3 AS
(
SELECT ID,
MAX(SYS_CONNECT_BY_PATH(NAME,' ')) AS NAME
FROM TB2
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1 AND PRIOR ID = ID
GROUP BY ID
)
SELECT ID,REPLACE(NAME,' ','') AS NAME
FROM TB3;