CODEWITHSUNDEEP
sqlsql-server
Jessica Warren
Jessica Warren

Reputation: 398

How do I make three columns into one column?

I have three 4 columns, three of which are values of different candies. I would like to see in one columns all of the candy listed:

Example below (using SQL Server):

Original Table:

    +--------------------+----------+---------+
    |  ID     |    Col1    | Col2    |
    +---------------------+----------+---------+
    | L123    |   TWIX     |         |       
     +--------------------+----------+---------+
    | L124    |  TWIX      |         |
    +--------------------+-----------+---------+        
    | L125    |            |   m&ms  |
    +--------------------+----------+---------+
    | F143    |            |   m&ms  |
    +--------------------+----------+---------+
    | F144     | gummies |          |
    +--------------------+----------+---------+
    | F144     | gummies |          | 
    +--------------------+----------+---------+

New Table:

I have tried union statements and case expression but they seem either way to long and complicated or they do not work. 

 +--------------------+
|  ID     |    Col1  |
+--------------------+
| L123    |   TWIX   |            
 +-------------------+
| L124    |  TWIX    |
+--------------------+      
| L125     |   m&ms  |
+--------------------+
| F143     | m&ms    |
+--------------------+
| F144     | gummies |          
+--------------------+
| F144     | gummies |          
+--------------------+

Upvotes: 0

Views: 159

Answers (2)

Jim Todd
Jim Todd

Reputation: 1588

I don't think your column data has spaces within the strings. In that case, you can do something like below:

Select ID, RTRIM(LTRIM(CONCAT(CONCAT(col1,,col2),,col3))) as one_col from table_name

The logic concatenates col 1, 2, 3. Finally removes the leading and trailing spaces from them. By that, you get your data in a single column from all the three columns whichever has an entry.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want :

select ID, coalesce(Col1, Col2, Col3) as Col1
from table t;

Upvotes: 2

Related Questions