sql selectL行到没有子查询的列 [英] sql selectL rows to colums without subquery

问题描述

我在 Oracle 10g 中有一个表，其中包含如下信息:

I have a table in Oracle 10g that contains some information as follows:

SQL> select * from t_test;

      INFO     CODIGO GRUPO
---------- ---------- ----------
       101        190 VTOS
       100        130 VTOS
       102        140 VTOS

我想提取所有具有 GRUPO='VTOS' 的行并将这些行转置为列.CODIGO 中的值是预先确定的(我知道可以在该列中存储哪些值)，所以想得到这样的东西:

I'd like to extract all the rows that have GRUPO='VTOS' and transpose those rows to columns. The values in CODIGO are predetermined(I know which values can be stored in that column), so would like to get something like this:

INFO_190 INFO_130 INFO_140
-------- -------- --------
     101      100      102

我知道我可以用一些子查询来做到这一点，例如:

I know I can do it with some subqueries, for example:

SELECT (SELECT info
          FROM t_test
         WHERE codigo = 190 AND grupo = 'VTOS') info_190,
       (SELECT info
          FROM t_test
         WHERE codigo = 130 AND grupo = 'VTOS') info_130,
       (SELECT info
          FROM t_test
         WHERE codigo = 140 AND grupo = 'VTOS') info_140
  FROM DUAL

但我想找到一种更有效的方法.谁能建议怎么做?

but I'd like to find a more efficient way. Can anyone suggest how to do it?

推荐答案

假设您的 codigo 值相对较少，则应该可以使用以下内容:

Assuming you have a relatively small number of codigo values, something like the following should work:

select max(decode(codigo, 190, info, '')) info_190,
 max(decode(codigo, 130, info, '')) info_130,
 max(decode(codigo, 140, info, '')) info_140
from t_test
where grupo = 'VTOS';

如果您有多个 grupo 值，请按 grupo 分组并将其包含在您的 select 子句中.

If you have multiple grupo values, group by grupo and include it in your select clause.

这篇关于sql selectL行到没有子查询的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！