将列转置为行oracle [英] transpose column to row oracle
问题描述
我有这种形式的查询返回值(查询返回超过50列).
I have a query returned value in this form (query return more than 50 columns).
1-99transval 100-200transval 200-300transval ... 1-99nontransval 100...
50 90 80 67 58
获取行值.我希望将这些详细信息转换为列并采用以下形状:
For a row value. I want these details to be converted into columns and take the following shape:
Range Transval NonTransval
1-99 50 67
100-200 90 58
推荐答案
在纯 SQL 中,由于需要手动放置范围,因为值和范围之间根本没有关系.如果存在关系,则可以使用 CASE 表达式并动态建立范围.
In pure SQL, it will need a lot of coding because you will have to manually put the range as there is no relation between the values and the range at all. Had there been a relationship, you could use CASE expression and build the range dynamically.
SQL> WITH DATA AS
2 (SELECT 50 "1-99transval",
3 90 "100-200transval",
4 80 "200-300transval",
5 67 "1-99nontransval",
6 58 "100-200nontransval",
7 88 "200-300nontransval"
8 FROM dual
9 )
10 SELECT '1-99' range,
11 "1-99transval" transval,
12 "1-99nontransval" nontransval
13 FROM DATA
14 UNION
15 SELECT '100-200' range,
16 "100-200transval",
17 "100-200nontransval" nontransval
18 FROM DATA
19 UNION
20 SELECT '200-300' range,
21 "200-300transval",
22 "200-300nontransval" nontransval
23 FROM DATA;
RANGE TRANSVAL NONTRANSVAL
------- ---------- -----------
1-99 50 67
100-200 90 58
200-300 80 88
在 Oracle数据库11g第1版及更高版本中,您可以使用 UNPIVOT
From Oracle database 11g Release 1 and above, you could use UNPIVOT
SQL> WITH DATA AS
2 (SELECT 50 "1-99transval",
3 90 "100-200transval",
4 80 "200-300transval",
5 67 "1-99nontransval",
6 58 "100-200nontransval",
7 88 "200-300nontransval"
8 FROM dual
9 )
10 SELECT *
11 FROM DATA
12 UNPIVOT( (transval,nontransval)
13 FOR RANGE IN ( ("1-99transval","1-99nontransval") AS '1-99'
14 ,("100-200transval","100-200nontransval") AS '100-200'
15 ,("200-300transval","200-300nontransval") AS '200-300'));
RANGE TRANSVAL NONTRANSVAL
------- ---------- -----------
1-99 50 67
100-200 90 58
200-300 80 88
在上述情况下,您需要将现有的查询中的 WITH 子句替换为子查询.您需要在 UNION 中添加其他列.
Above, in your case you need to replace the WITH clause with your existing query as a sub-query. You need to include other columns in the UNION.
在 PL/SQL 中,您可以(ab)使用立即执行并通过在<动态SQL .
In PL/SQL, you could (ab)use EXECUTE IMMEDIATE and get the "range" by extracting the column names in dynamic sql.
尽管如此,修改/重写尚未显示的现有查询会更好.
Although, it would be much better to modify/rewrite your existing query which you have not shown yet.
这篇关于将列转置为行oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!