单个SQL SELECT从一个表行返回多个行

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语句中是否有一种聪明的方法(即没有UNION)?列名称Value1,Value2和Value3是固定且常量的。

The database is oracle 9i.

该数据库是oracle 9i。

9 个解决方案

#1

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来自双重)”。对于Oracle来说,滥用递归查询更为通用。 (但是,使用联合生成行可以移植到其他数据库)

#2

Give a union a shot.

给工会一个机会。

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 all means that the server won't perform a distinct (which is implicit in union operations). 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.

使用union all意味着服务器不会执行distinct(在union操作中隐含)。它不应该对数据产生任何影响(因为你的ID应该是HOPEFULLY不同),但它可能会加快一点。

#3

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

#4

Unioning three select statements should do the trick:

联合三个select语句应该可以解决问题:

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

#5

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

#6

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中最好的选择。您可能还需要考虑在前端处理此问题,具体取决于您的具体要求。

#7

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语句是普通的vanilla SQL,它将存在于任何关系数据库中。

#8

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用于什么,但你不能把它留下来。

#9

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语句是普通的vanilla 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以便将其与“值”连接。

#1