想要动态拆分查询结果中的列,我该怎么做? 我有三个表并连接 them.to 获得所需的列。
select distinct t1.sal,t1.cat,t2.id,t3.shop
from t_table1 t1,
t_table2 t2,
t_table3 t3,
where t1.sno=t2.sno AND
t2.cat=t3.cat
t3.dept_no=t3.dept_no
**output:**
t1.sal t1.cat t2.id t3.shop
1900 34R5 10 dense
1900 34r5 10 SVM
1900 34r5 10 bpo
2345 3ER4 11 kpo
2345 3ER4 11 infra
12345 34F4 12 const
**desired output:**
t1.sal t1.cat t1.tin t2.id t3.shop_1 t3.shop_2 t3.shop_3
1900 34r5 23456 10 dense svm bpo
2345 3er4 6543 11 kpo infra null
12345 34f4 34556 12 const null null
使用PIVOT
和ROW_NUMBER
分析函数:
SQL 小提琴
Oracle 11g R2 架构设置:
CREATE TABLE your_query_result (sal, cat, id, shop ) AS
SELECT 1900, '34R5', 10, 'dense' FROM DUAL UNION ALL
SELECT 1900, '34r5', 10, 'SVM' FROM DUAL UNION ALL
SELECT 1900, '34r5', 10, 'bpo' FROM DUAL UNION ALL
SELECT 2345, '3ER4', 11, 'kpo' FROM DUAL UNION ALL
SELECT 2345, '3ER4', 11, 'infra' FROM DUAL UNION ALL
SELECT 12345, '34F4', 12, 'const' FROM DUAL;
查询 1:
SELECT sal,
cat,
id,
"1_SHOP" AS shop_1,
"2_SHOP" AS shop_2,
"3_SHOP" AS shop_3
FROM (
SELECT r.*,
ROW_NUMBER() OVER (
PARTITION BY sal, cat, id
ORDER BY shop
) AS rn
FROM (
SELECT * FROM your_query_result
) r
)
PIVOT (
MAX( shop ) AS shop
FOR rn IN ( 1, 2, 3 )
)
结果:
| SAL | CAT | ID | SHOP_1 | SHOP_2 | SHOP_3 |
|-------|------|----|--------|--------|--------|
| 1900 | 34R5 | 10 | dense | (null) | (null) |
| 1900 | 34r5 | 10 | SVM | bpo | (null) |
| 2345 | 3ER4 | 11 | infra | kpo | (null) |
| 12345 | 34F4 | 12 | const | (null) | (null) |
如果您希望第一行和第二行在同一行上,则只需将CAT
列更改为小写,然后再生成ROW_NUMBER
并PIVOT
ing。