单个 SQL SELECT 从一个表行返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/887870/
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
Single SQL SELECT Returning multiple rows from one table row
提问by James L
We have a table which is of the form:
我们有一个表格，其形式为：
ID,Value1,Value2,Value3
1,2,3,4
We need to transform this into.
我们需要将其转化为。
ID,Name,Value
1,'Value1',2
1,'Value2',3
1,'Value3',4
Is there a clever way of doing this in one SELECT statement (i.e without UNIONs)? The column names Value1,Value2 and Value3 are fixed and constant.
在一个 SELECT 语句（即没有联合）中是否有一种聪明的方法来做到这一点？列名 Value1、Value2 和 Value3 是固定不变的。
The database is oracle 9i.
数据库是oracle 9i。
采纳答案by araqnid
This works on Oracle 10g:
这适用于 Oracle 10g：
select id, 'Value' || n as name,
case n when 1 then value1 when 2 then value2 when 3 then value3 end as value
from (select rownum n
from (select 1 from dual connect by level <= 3)) ofs, t
I think Oracle 9i had recursive queries? Anyway, I'm pretty sure it has CASE support, so even if it doesn't have recursive queries, you can just do "(select 1 from dual union all select 2 from dual union all select 3 from dual) ofs" instead. Abusing recursive queries is a bit more general- for Oracle. (Using unions to generate rows is portable to other DBs, though)
我认为 Oracle 9i 有递归查询？无论如何，我很确定它具有 CASE 支持，因此即使它没有递归查询，您也可以改为执行“（从双重联合中选择 1，从双重联合中选择 2，从双重联合中选择 3，从双重中选择 3）”。滥用递归查询更为普遍——对于 Oracle。（不过，使用联合生成行可以移植到其他数据库中）
回答by Adam Robinson
Give a uniona shot.
试一试union。
select ID, 'Value1' as Name, Value1 as Value from table_name union all
select ID, 'Value2', Value2 as Value from table_name union all
select ID, 'Value3', Value3 as Value from table_name
order by ID, Name
using union allmeans that the server won't perform a distinct(which is implicit in unionoperations). It shouldn't make any difference with the data (since your ID's should HOPEFULLY be different), but it might speed it up a bit.
usingunion all意味着服务器不会执行 a distinct（这是union操作中隐含的）。它不应该对数据产生任何影响（因为您的 ID 应该有所不同），但它可能会加快速度。
回答by Dave Webb
You can do it like this, but it's not pretty:
你可以这样做，但它并不漂亮：
SELECT id,'Value 1' AS name,value1 AS value FROM mytable
UNION
SELECT id,'Value 2' AS name,value2 AS value FROM mytable
UNION
SELECT id,'Value 3' AS name,value3 AS value FROM mytable
回答by dpmattingly
Unioning three select statements should do the trick:
联合三个选择语句应该可以解决问题：
SELECT ID, 'Value1', Value1 AS Value
FROM TABLE
UNION
SELECT ID, 'Value2', Value2 AS Value
FROM TABLE
UNION
SELECT ID, 'Value3', Value3 AS Value
FROM TABLE
回答by Robin Day
If you're using SQL Server 2005+ then you can use UNPIVOT
如果您使用的是 SQL Server 2005+，则可以使用 UNPIVOT
CREATE TABLE #tmp ( ID int, Value1 int, Value2 int, Value3 int)
INSERT INTO #tmp (ID, Value1, Value2, Value3) VALUES (1, 2, 3, 4)
SELECT
*
FROM
#tmp
SELECT
*
FROM
#tmp
UNPIVOT
(
[Value] FOR [Name] IN (Value1, Value2, Value3)
) uPIVOT
DROP TABLE #tmp
回答by Tom H
A UNION ALL, as others have suggested, is probably your best bet in SQL. You might also want to consider handling this in the front end depending on what your specific requirements are.
正如其他人所建议的那样，UNION ALL 可能是您在 SQL 中的最佳选择。您可能还需要考虑在前端处理此问题，具体取决于您的具体要求。
回答by Jake Orshansky
CTE syntax may be different for Oracle (I ran it in Teradata), but I only used CTE to provide test data, those 1 2 3 and 4. You can use temp table instead. The actual select statement is plain vanilla SQL and it will on any relational database.
Oracle 的 CTE 语法可能有所不同（我在 Teradata 中运行它），但我仅使用 CTE 来提供测试数据，即 1 2 3 和 4。您可以改用临时表。实际的 select 语句是普通的普通 SQL，它适用于任何关系数据库。
回答by Jake Orshansky
Try this:
尝试这个：
CTE creates a temp table with 4 values. You can run this as is in any database.
CTE 创建一个包含 4 个值的临时表。您可以在任何数据库中按原样运行它。
with TEST_CTE (ID) as
(select * from (select '1' as a) as aa union all
select * from (select '2' as b) as bb union all
select * from (select '3' as c) as cc union all
select * from (select '4' as d) as dd )
select a.ID, 'Value'|| a.ID, b.ID
from TEST_CTE a, TEST_CTE b
where b.ID = (select min(c.ID) from TEST_CTE c where c.ID > a.ID)
Here is the result set:
这是结果集：
1 Value1 2
2 Value2 3
3 Value3 4
Enjoy!
享受！
Some afterthoughts.
一些事后的想法。
^^^ CTE syntax may be different in Oracle. I could only run it in Teradata. You can substitute it with temp table or fix the syntax to make it Oracle compatible. The select statement is plain vanilla SQL that will work on any database.
^^^ CTE 语法在 Oracle 中可能有所不同。我只能在 Teradata 中运行它。您可以将其替换为临时表或修复语法以使其与 Oracle 兼容。select 语句是可以在任何数据库上工作的普通 SQL。
^^^ Another thing to note. If ID field is numeric, you might need to cast it into CHAR in order to concatenate it with "Value".
^^^ 还有一点要注意。如果 ID 字段是数字，您可能需要将其转换为 CHAR 以将其与“值”连接。
回答by Andomar
For Sql Server, consider UNPIVOT as an alternative to UNION:
对于 Sql Server，考虑 UNPIVOT 作为 UNION 的替代：
SELECT id, value, colname
FROM #temp t
UNPIVOT (Value FOR ColName IN (value1,value2,value3)) as X
This will return the column name as well. I'm unsure what the X is used for, but you can't leave it out.
这也将返回列名。我不确定 X 是做什么用的，但你不能忽略它。
