在Oracle 9i中将列转换为行 [英] Convert columns to rows in oracle 9i
问题描述
请帮助我使用 SQL Oracle 9i ,按ID将列转换为组中的行.
我无法在Oracle 9i中使用UNPIVOT进行转换.而且我不会使用UNION ALL,因为以后使用太多的代码.
来自此列表:
表格:
员工
ID |数据1 |数据2 |数据3 |数据4 ||
-------------------------------------------------- --------
1 | 0 | 1 | 3 | 2 |
2 | 1 | 0 | 3 | 0 |
Please help me to the SQL Oracle 9i, convert columns to rows in group by ID.
I can't convert using UNPIVOT in oracle 9i. And I will not use UNION ALL, it's too much code to use later.
From this list:
Table:
Employees
ID | DATA 1 | DATA 2 | DATA 3 | DATA 4 |
----------------------------------------------------------
1 | 0 | 1 | 3 | 2 |
2 | 1 | 0 | 3 | 0 |
结果
ID | SOURCE |数据 |
--------------------------------------
1 | 数据2 | 1 |
1 | 数据4 | 2 |
1 | 数据3 | 3 |
2 | 数据1 | 1 |
2 | 数据3 | 3 |
Result
ID | SOURCE | DATA |
--------------------------------------
1 | DATA 2 | 1 |
1 | DATA 4 | 2 |
1 | DATA 3 | 3 |
2 | DATA 1 | 1 |
2 | DATA 3 | 3 |
推荐答案
以下是手动取消透视的一种方法:
Here's a way of doing a manual unpivot:
WITH employees AS (SELECT 1 ID, 0 data1, 1 data2, 3 data3, 2 data4 FROM dual UNION ALL
SELECT 2 ID, 1 data1, 0 data2, 3 data3, 0 data4 FROM dual)
SELECT e.ID,
CASE WHEN d.id = 1 THEN 'DATA 1'
WHEN d.id = 2 THEN 'DATA 2'
WHEN d.id = 3 THEN 'DATA 3'
WHEN d.id = 4 THEN 'DATA 4'
END SOURCE,
CASE WHEN d.id = 1 THEN data1
WHEN d.id = 2 THEN data2
WHEN d.id = 3 THEN data3
WHEN d.id = 4 THEN data4
END DATA
FROM employees e
CROSS JOIN (SELECT LEVEL ID
FROM dual
CONNECT BY LEVEL <= 4 -- the number of columns to unpivot
) d
WHERE CASE WHEN d.id = 1 THEN data1
WHEN d.id = 2 THEN data2
WHEN d.id = 3 THEN data3
WHEN d.id = 4 THEN data4
END > 0
ORDER BY ID,
DATA;
ID SOURCE DATA
---------- ------ ----------
1 DATA 2 1
1 DATA 4 2
1 DATA 3 3
2 DATA 1 1
2 DATA 3 3
这将使用一个4行的虚拟表"(因为您不取消透视4列)来交叉连接到主表.然后只需为第1行输出data1,为第2行输出data2,等等.
This uses a dummy "table" of 4 rows (since you're unpivoting 4 columns) to cross join to the main table. Then it's just a matter of outputting data1 for row 1, data2 for row 2, etc.
我在where子句中再次使用了case语句,但是您可以在子查询中没有谓词的情况下执行查询,然后根据需要在外部查询中添加过滤器(例如select id, source, data from (select e.id, case .... ) where data > 0
)
I've used the case statement again in the where clause, but you could do the query without the predicate in a subquery and then add the filter in the outer query if you like (e.g. select id, source, data from (select e.id, case .... ) where data > 0
)
这篇关于在Oracle 9i中将列转换为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!