如何获取表列名称作为结果列集中的条目? [英] how to get the table column names as entries in result column set?

问题描述

数据库方案包括:

Table1(代码，col1，col2，col3，col4，col5)

Table1(code, col1, col2, col3, col4, col5)

1. 要做的是: 对于具有来自Table1表的最大代码值的Table1，请在两列中获取其所有特征(代码除外):
   • 特征名称(PC表中相应列的名称)；
   • 特征值.

1. What to do is: For the Table1 with the maximal code value from Table1 table, obtain all its characteristics (except for a code) in two columns:
   • The name of the characteristic (a name of a corresponding column in the PC table);
   • Value of the characteristic.

我不知道如何在结果列集中获取表列名称. 最终结果将如下所示:

I don't have any idea how to get the table column names in my result column set. The final result will look like:

chr value
col1    133
col2    80
col3    28
col4    2
col5    50

推荐答案

这是不可操作的操作.最简单的方法是使用union all.但是，以下通常更有效:

This is an unpivot operation. The simplest way is using union all. However, the following is generally more efficient:

select (case when n.n = 1 then 'col1'
             when n.n = 2 then 'col2'
             when n.n = 3 then 'col3'
             when n.n = 4 then 'col4'
             when n.n = 5 then 'col5'
        end) as chr,
       (case when n.n = 1 then col1
             when n.n = 2 then col2
             when n.n = 3 then col3
             when n.n = 4 then col4
             when n.n = 5 then col5
        end) as value
from table t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n;

当表很大或复杂的子查询时，这种方法效率更高.

This is more efficient when your table is big or a complicated subquery.

union all版本为:

select 'col1', col1 from table t union all
select 'col2', col2 from table t union all
select 'col3', col3 from table t union all
select 'col4', col4 from table t union all
select 'col5', col5 from table t;

这篇关于如何获取表列名称作为结果列集中的条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！