oracle 在oracle中通过concat文本选择列作为列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2961538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select columns by a concat text as columnname in oracle
提问by glaudiston
I have a table with columns named with the number of hour of day like this:
我有一个表，其中的列以一天中的小时数命名，如下所示：
col00 NUMBER(5)
col01 NUMBER(5)
col02 NUMBER(5)
...
col23 NUMBER(5)
...and I have another query that returns a count by hour.
...我还有另一个查询，按小时返回计数。
I want to recover the colXX value by hour.... then I can recover with "decode" or "case when..." but I want know if exists any way to recover the column by a text like this:
我想按小时恢复 colXX 值......然后我可以用“解码”或“情况...”恢复，但我想知道是否存在任何方法来通过这样的文本恢复列：
select "col"||hour from table;
in the hypothetical above example if hour is 13 then would be translated like:
在上面的假设示例中，如果小时为 13，则将被翻译为：
select col13 from table;
there is any way to do this ?
有没有办法做到这一点？
采纳答案by glaudiston
Closing...
关闭...
select REGEXP_REPLACE(
REGEXP_REPLACE( DBMS_XMLGEN.GETXML('
SELECT col'|| to_char(sysdate,'HH24') || ' FROM TABLE
'),'.*<[/]?(\?xml .*>|ROW).*',''),' (<([^>]*)>([^<]*)</[^>]*>.*)','=') AS RES from dual
its not so simple but very flexible and works great for me.
它不是那么简单，但非常灵活，对我来说非常有用。
回答by OMG Ponies
You have to use dynamic SQL:
您必须使用动态 SQL：
EXECUTE IMMEDIATE 'SELECT col'|| hour || ' FROM TABLE;'
Reference:
参考：
回答by Mark Bowytz
While it looks a little...brute force...you could use a bunch of UNION statements between queries:
虽然它看起来有点......蛮力......你可以在查询之间使用一堆 UNION 语句：
SELECT col00
FROM TABLE
WHERE to_char(SYSDATE, 'HH24') = '00'
UNION
SELECT col01
FROM TABLE
WHERE to_char(SYSDATE, 'HH24') = '01'
UNION
SELECT col02
FROM TABLE
WHERE to_char(SYSDATE, 'HH24') = '02'
(...and so on...)
UNION
SELECT col23
FROM TABLE
WHERE to_char(SYSDATE, 'HH24') = '23'
