DROP TABLE tt; CREATE TABLE tt(ID INT,a1 INT,b1 INT,a2 INT,b2 INT,a3 INT,b3 INT,a4 INT,b4 INT); INSERT INTO tt VALUES(1,2,201,1,204,1,202,1,203); INSERT INTO tt VALUES(2,1,201,1,204,2,202,1,203); SELECT * FROM tt;SELECT ID, SUM(DECODE(RN, 1, A1)) a, SUM(DECODE(RN, 1, B1)) b, SUM(DECODE(RN, 2, A1)) c, SUM(DECODE(RN, 2, B1)) d, SUM(DECODE(RN, 3, A1)) e, SUM(DECODE(RN, 3, B1)) f, SUM(DECODE(RN, 4, A1)) g, SUM(DECODE(RN, 4, B1)) h FROM (SELECT ID, A1, B1, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY A1, B1) RN FROM (SELECT ID, A1, B1 FROM TT UNION ALL SELECT ID, A2, B2 FROM TT UNION ALL SELECT ID, A3, B3 FROM TT UNION ALL SELECT ID, A4, B4 FROM TT)) GROUP BY ID;
输出: ID A B C D E F G H 1 202 1 203 1 204 2 201 1 201 1 203 1 204 2 202
首先对ROW_NUMBER() OVER(PARTITION BY ID ORDER BY A1, B1)解释一下 ROW_NUMBER() 是分析函数 显示记录行数 OVER(PARTITION BY ID ORDER BY A1, B1) 是开窗函数 和分析函数合用说明是按照各自ID范围进行顺序排列SELECT ID, A1, B1 FROM TT UNION ALL SELECT ID, A2, B2 FROM TT UNION ALL SELECT ID, A3, B3 FROM TT UNION ALL SELECT ID, A4, B4 FROM TT 语句是把列数据转换成统一行数据 ID A1 B1 1 2 201 1 1 202 1 1 203 1 1 204 2 2 202 2 1 201 2 1 203 2 1 204 通过ROW_NUMBER() OVER(PARTITION BY ID ORDER BY A1, B1)语句 可得到 ID A1 B1 rn 1 1 202 1 1 1 203 2 1 1 204 3 1 2 201 42 1 201 1 2 1 203 2 2 1 204 3 2 2 202 4 然后通过decode把行转换为列
DROP TABLE tt;
CREATE TABLE tt(ID INT,a1 INT,b1 INT,a2 INT,b2 INT,a3 INT,b3 INT,a4 INT,b4 INT);
INSERT INTO tt VALUES(1,2,201,1,204,1,202,1,203);
INSERT INTO tt VALUES(2,1,201,1,204,2,202,1,203);
SELECT * FROM tt;SELECT ID,
SUM(DECODE(RN, 1, A1)) a,
SUM(DECODE(RN, 1, B1)) b,
SUM(DECODE(RN, 2, A1)) c,
SUM(DECODE(RN, 2, B1)) d,
SUM(DECODE(RN, 3, A1)) e,
SUM(DECODE(RN, 3, B1)) f,
SUM(DECODE(RN, 4, A1)) g,
SUM(DECODE(RN, 4, B1)) h
FROM (SELECT ID,
A1,
B1,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY A1, B1) RN
FROM (SELECT ID, A1, B1
FROM TT
UNION ALL
SELECT ID, A2, B2
FROM TT
UNION ALL
SELECT ID, A3, B3
FROM TT
UNION ALL
SELECT ID, A4, B4 FROM TT))
GROUP BY ID;
输出:
ID A B C D E F G H
1 202 1 203 1 204 2 201
1 201 1 203 1 204 2 202
ROW_NUMBER() 是分析函数 显示记录行数
OVER(PARTITION BY ID ORDER BY A1, B1) 是开窗函数 和分析函数合用说明是按照各自ID范围进行顺序排列SELECT ID, A1, B1
FROM TT
UNION ALL
SELECT ID, A2, B2
FROM TT
UNION ALL
SELECT ID, A3, B3
FROM TT
UNION ALL
SELECT ID, A4, B4 FROM TT 语句是把列数据转换成统一行数据
ID A1 B1
1 2 201
1 1 202
1 1 203
1 1 204
2 2 202
2 1 201
2 1 203
2 1 204
通过ROW_NUMBER() OVER(PARTITION BY ID ORDER BY A1, B1)语句
可得到
ID A1 B1 rn
1 1 202 1
1 1 203 2
1 1 204 3
1 2 201 42 1 201 1
2 1 203 2
2 1 204 3
2 2 202 4
然后通过decode把行转换为列