我在oracle中显示记录有问题。
例如:我将所有记录显示在一行中,如:
id| a | a1 | b | b1
-------------------
1 |400| 1 |410| 3
2 |400| 10 |410| 30
但是我需要,记录看起来像:
id| code| number
----------------
1 | 400 | 1
1 | 410 | 3
2 | 400 | 10
2 | 410 | 30
也许我应该按功能使用组?
请帮助我。
这在Oracle
上正常工作select id ,a "code" , a1 "number" from test
union all
select id, b "code",b1 "number" from test
order by id
demo
使用Oracle的Undivot功能,您可以如下实现此目标
WITH table_ (id, a, a1, b, b1) AS (
SELECT 1, 400, 1, 410, 3 FROM dual UNION ALL
SELECT 3, 300, 4, 310, 5 FROM dual UNION ALL
SELECT 2, 400, 10, 410, 30 from dual)
---------
-- End of data preparation
---------
SELECT id,
CASE
WHEN a1 = 'A' THEN
a
WHEN a1 = 'B' THEN
b
ELSE
NULL
END AS code,
"NUMBER"
FROM table_ unpivot("NUMBER" FOR a1 IN(a1 AS 'A', b1 AS 'B'));
输出:
| ID | CODE | NUMBER |
|----|------|--------|
| 1 | 400 | 1 |
| 1 | 410 | 3 |
| 3 | 300 | 4 |
| 3 | 310 | 5 |
| 2 | 400 | 10 |
| 2 | 410 | 30 |
可能带有 unpivot
的语法,特别是如果您有更多列(C,C1,D,D1 ...):
select id, code, num from test
unpivot ((code, num) for (x, y) in ((a, a1), (b, b1)))
sqlfiddle demo