Select values from multiple columns into single column

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:

  • SQL: compare two table record by record
  • How to use SQL Server Database Project
  • SELECT COUNT(DISTINCT ) from several tables
  • What is the significance of 1/1/1753 in SQL Server?
  • sqlpackage.exe: any way to ignore users on export?
  • Is GO allowed in SQL files passed to DbMigration.SqlFile?
  • +---------+------+--------+------+
    |   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:

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

    3 Solutions collect form web for “Select values from multiple columns into single column”

    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;
    

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

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.