CODEWITHSUNDEEP
sqlrows
eatonphil
eatonphil

Reputation: 13672

SQL - Combine multiple columns into one column with more rows

I have a table with 20 columns that all display the same thing. I'm not sure why my company set it up like this, but I cannot make changes to the table.

With that in mind, here is what I need to do. I need to populate a drop down list with insurance company names. Therefore I need to find unique values across the entire table.

Using a Group By clause is out of the question because I need unique values across the entire table. No single column contains all the possible values. My only thought was to combine all the columns of the table together. I've seen this done using two pipes ( || ). But that concatenates the columns which does not help me.

I need to join two (or twenty) columns together and add their rows together. I.e. if I started out with 20 columns and 100 rows, I need to have one column with 2000 rows. This way I can select unique values using the Group By clause.

Any help would be greatly appreciated!

Sample of what I'm trying to accomplish:

Sample original table:

    --Ins1-----Ins2---Ins3---Ins4-
    Medicaid-Medicare-------------
    ---------Medicaid-----No 485--
    Blue Cross--------------------
    -------Home Health----Medicare

Table I need to construct:

    --Column1--
    -Medicaid--
    -----------
    Blue Cross-
    -----------
    -Medicare--
    -Medicaid--
    -----------
    Home Health
    -----------
    -----------
    -----------
    -----------
    -----------
    --No 485---
    -----------
    -Medicare--

Maybe my logic is wrong. This is the only way I could see to find unique information across the entire table.

Upvotes: 0

Views: 7859

Answers (1)

Taryn
Taryn

Reputation: 247670

If this is SQL Server, then it sounds like you need to use UNPIVOT to perform this transformation.

Sample UNPIVOT (See SQL Fiddle with Demo):

select ques, answer
FROM t1
unpivot
(
    answer
    for ques in (col1, col2, col3, col4)
) u

The UNPIVOT will transform your data from columns to rows.

Upvotes: 5

Related Questions