Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

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

like image 291
Jason Avatar asked Oct 22 '13 10:10

Jason


People also ask

How do I select multiple columns as single column in SQL?

Luckily, SQL makes this really easy. To select multiple columns from a table, simply separate the column names with commas!

How do I combine data from multiple columns into one in SQL?

Select the same number of columns for each query. Corresponding columns must be the same general data type. Corresponding columns must all either allow null values or not allow null values. If you want to order the columns, specify a column number because the names of the columns you are merging are probably different.


2 Answers

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
like image 81
Taryn Avatar answered Oct 12 '22 17:10

Taryn


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;
like image 21
LS_ᴅᴇᴠ Avatar answered Oct 12 '22 19:10

LS_ᴅᴇᴠ