从多列中选择值到单列 [英] Select values from multiple columns into single column

问题描述

我在数据库中有一个表，它有 9 列包含相同类型的数据，这些值允许为空.我需要将每个非空值选择到一列值中，这些值不关心它们源自的行的身份.

I have a table in a database that has 9 columns containing the same sort of data, these values are allowed to be null. I need to select each of the non-null values into a single column of values that don't care about the identity of the row from which they originated.

所以，对于看起来像这样的表格:

So, for a table that looks like this:

+---------+------+--------+------+
|   Id    | I1   | I2     | I3   | 
+---------+------+--------+------+
|    1    | x1   | x2     |  x7  |
|    2    | x3   | null   |  x8  |
|    3    | null | null   |  null|
|    4    | x4   | x5     |  null|
|    5    | null | x6     |  x9  |
+---------+------+--------+------+

我希望将每个以 x 为前缀的值选择到一个列中.我的结果数据应如下表所示.需要保留顺序，所以第一行的第一列值应该在顶部，最后一行的最后一列值应该在底部:

I wish to select each of the values prefixed with x into a single column. My resultant data should look like the following table. The order needs to be preserved, so the first column value from the first row should be at the top and the last column value from the last row at the bottom:

+-------+
| value |
+-------+
|  x1   |
|  x2   |
|  x7   |
|  x3   |
|  x8   |
|  x4   |
|  x5   |
|  x6   |
|  x9   |
+-------+

我使用的是 SQL Server 2008 R2.是否有比从每一行依次选择每一列的值并将非空值插入结果中更好的技术来实现这一点?

I am using SQL Server 2008 R2. Is there a better technique for achieving this than selecting the value of each column in turn, from each row, and inserting the non-null values into the results?

推荐答案

可以使用UNPIVOT函数得到最终结果:

You can use the UNPIVOT function to get the final result:

select value
from yourtable
unpivot
(
  value
  for col in (I1, I2, I3)
) un
order by id, col;

由于您使用的是 SQL Server 2008+，那么您还可以使用带有 VALUES 子句的 CROSS APPLY 来取消旋转列:

Since you are using SQL Server 2008+, then you can also use CROSS APPLY with the VALUES clause to unpivot the columns:

select value
from yourtable
cross apply
(
    values
        ('I1', I1),
        ('I2', I2),
        ('I3', I3)
) c(col, value)
where value is not null
order by id, col

这篇关于从多列中选择值到单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！