从多个列中选择值到单个列中

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.

我有一个数据库中的表，它有9列包含相同类型的数据，这些值允许为null。我需要将每个非空值都选择为一列值，这些值不关心它们产生的行的标识。

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  |
+---------+------+--------+------+

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:

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

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

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?

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

3 个解决方案

#1

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

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

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

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

既然您正在使用SQL Server 2008+，那么您还可以使用CROSS APPLY with the VALUES子句来取消列的数据透视:

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

#2

SELECT value FROM (
   SELECT ID, 1 AS col, I1 AS [value] FROM t
   UNION ALL SELECT ID, 2,  I2 FROM t
   UNION ALL SELECT ID, 3,  I3 FROM t
) AS t WHERE value IS NOT NULL ORDER BY ID, col;

#3

Try union like below :

试试以下的联合:

    select value from
(
    select col1 as value from TestTable
    union
    select col2 as value from TestTable
    union
    select col3 as value from TestTable
) tt where value is not null

#1