Oracle - select 语句中的动态列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/860516/
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
Oracle - dynamic column name in select statement
提问by Americus
Question:
题：
Is it possible to have a column name in a select statement changed based on a value in it's result set?
是否可以根据结果集中的值更改选择语句中的列名？
For example, if a yearvalue in a result set is less than 1950, name the column OldYear, otherwise name the column NewYear. The yearvalue in the result set is guaranteed to be the same for all records.
例如，如果year结果集中的值小于1950，则命名列OldYear，否则命名列NewYear。该year结果集中的值是保证所有记录相同。
I'm thinking this is impossible, but here was my failed attempt to test the idea:
我认为这是不可能的，但这是我测试这个想法的失败尝试：
select 1 as
(case
when 2 = 1 then "name1";
when 1 = 1 then "name2")
from dual;
采纳答案by Bill Karwin
You can't vary a column name per row of a result set. This is basic to relational databases. The names of columns are part of the table "header" and a name applies to the column under it for all rows.
您不能改变结果集每行的列名。这是关系数据库的基础。列的名称是表“标题”的一部分，并且名称适用于其下所有行的列。
Re comment: OK, maybe the OP Americus means that the result is known to be exactly one row. But regardless, SQL has no syntax to support a dynamic column alias. Column aliases must be constant in a query.
重新评论：好的，也许 OP Americus 意味着已知结果正好是一行。但无论如何，SQL 没有支持动态列别名的语法。列别名在查询中必须是常量。
Even dynamic SQL doesn't help, because you'd have to run the query twice. Once to get the value, and a second time to re-run the query with a different column alias.
即使是动态 SQL 也无济于事，因为您必须运行查询两次。一次获取值，第二次使用不同的列别名重新运行查询。
回答by Thought
The "correct" way to do this in SQL is to have both columns, and have the column that is inappropriate be NULL, such as:
在 SQL 中执行此操作的“正确”方法是同时拥有两列，并将不合适的列设为NULL，例如：
SELECT
CASE WHEN year < 1950 THEN year ELSE NULL END AS OldYear,
CASE WHEN year >= 1950 THEN year ELSE NULL END AS NewYear
FROM some_table_with_years;
回答by Jeffrey Kemp
There is no good reason to change the column name dynamically - it's analogous to the name of a variable in procedural code - it's just a label that you might refer to later in your code, so you don't want it to change at runtime.
没有充分的理由动态更改列名 - 它类似于过程代码中的变量名称 - 它只是您稍后可能在代码中引用的标签，因此您不希望它在运行时更改。
I'm guessing what you're really after is a way to format the output (e.g. for printing in a report) differently depending on the data. In that case I would generate the heading text as a separate column in the query, e.g.:
我猜您真正想要的是一种根据数据以不同方式格式化输出（例如，用于在报告中打印）的方法。在这种情况下，我会将标题文本生成为查询中的单独列，例如：
SELECT 1 AS mydata
,case
when 2 = 1 then 'name1'
when 1 = 1 then 'name2'
end AS myheader
FROM dual;
Then the calling procedure would take the values returned for mydata and myheader and format them for output as required.
然后调用过程将获取为 mydata 和 myheader 返回的值，并根据需要将它们格式化为输出。
回答by JKimmel
This solution requires that you launch SQLPLUS and a .sql file from a .bat file or using some other method with the appropriate Oracle credentials. The .bat file can be kicked off manually, from a server scheduled task, Control-M job, etc...
此解决方案要求您从 .bat 文件或使用其他具有适当 Oracle 凭据的方法启动 SQLPLUS 和 .sql 文件。.bat 文件可以从服务器计划任务、Control-M 作业等手动启动...
Output is a .csv file. This also requires that you replace all commas in the output with some other character or risk column/data mismatch in the output.
输出是一个 .csv 文件。这还要求您将输出中的所有逗号替换为输出中的某些其他字符或风险列/数据不匹配。
The trick is that your column headers and data are selected in two different SELECT statements.
诀窍是您的列标题和数据是在两个不同的 SELECT 语句中选择的。
It isn't perfect, but it does work, and it's the closest to standard Oracle SQL that I've found for a dynamic column header outside of a development environment. We use this extensively to generate recurring daily/weekly/monthly reports to users without resorting to a GUI. Output is saved to a shared network drive directory/Sharepoint.
它并不完美，但确实有效，而且它是我为开发环境之外的动态列标题找到的最接近标准 Oracle SQL 的方法。我们广泛使用它来为用户生成重复的每日/每周/每月报告，而无需求助于 GUI。输出保存到共享网络驱动器目录/Sharepoint。
REM BEGIN runExtract1.bat file -----------------------------------------
sqlplus username/password@database @C:\DailyExtracts\Extract1.sql > C:\DailyExtracts\Extract1.log
exit
REM END runExtract1.bat file -------------------------------------------
REM BEGIN Extract1.sql file --------------------------------------------
set colsep ,
set pagesize 0
set trimspool on
set linesize 4000
column dt new_val X
select to_char(sysdate,'MON-YYYY') dt from dual;
spool c:\DailyExtracts\&X._Extract1.csv
select '&X-Project_id', 'datacolumn2-Project_Name', 'datacolumn3-Plant_id' from dual;
select
PROJ_ID
||','||
replace(PROJ_NAME,',',';')-- "Project Name"
||','||
PLANT_ID-- "Plant ID"
from PROJECTS
where ADDED_DATE >= TO_DATE('01-'||(select to_char(sysdate,'MON-YYYY') from dual));
spool off
exit
/
REM ------------------------------------------------------------------
CSV OUTPUT (opened in Excel and copy/pasted):
old 1: select '&X-Project_id' 'datacolumn2-Project_Name' 'datacolumn3-Plant_id' from dual
new 1: select 'MAR-2018-Project_id' 'datacolumn2-Project_Name' 'datacolumn3-Plant_id' from dual
MAR-2018-Project_id datacolumn2-Project_Name datacolumn3-Plant_id
31415 name1 1007
31415 name1 2032
32123 name2 3302
32123 name2 3384
32963 name3 2530
33629 name4 1161
34180 name5 1173
34180 name5 1205
...
...
etc...
135 rows selected.
回答by Irfan Mulic
You will need something similar to this:
您将需要类似的东西：
select 'select ' || CASE WHEN YEAR<1950 THEN 'OLDYEAR' ELSE 'NEWYEAR' END || ' FROM TABLE 1' from TABLE_WITH_DATA
