单个SQL SELECT从一个表行返回多行 [英] Single SQL SELECT Returning multiple rows from one table row

问题描述

我们有一个表格，其形式为:

We have a table which is of the form:

ID,Value1,Value2,Value3
1,2,3,4

我们需要将其转换为.

ID,Name,Value
1,'Value1',2
1,'Value2',3
1,'Value3',4

在一个SELECT语句(即没有UNION)中是否有一种巧妙的方法?列名称Value1，Value2和Value3是固定且恒定的.

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.

数据库是oracle 9i.

The database is oracle 9i.

推荐答案

这适用于Oracle 10g:

This works on 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

我认为Oracle 9i有递归查询吗?无论如何，我非常确定它具有CASE支持，因此即使它没有递归查询，也可以执行(从双联合中选择1，从双联合中选择2，从双联合中选择3，从双重中选择3)"代替.对于Oracle，滥用递归查询会更普遍一些. (不过，使用联合来生成行可移植到其他数据库中)

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)

这篇关于单个SQL SELECT从一个表行返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！