Joining column names to column values

Question

I have a table that looks like this

col1 | col2
-----------
  1  |  a
  2  |  b
  3  |  c

and I want to run a query that makes it look like this

val | colname
-------------
  1 | col1
  2 | col1
  3 | col1
  a | col2
  b | col2
  c | col2

I've read about unpivot and I've figured out how to get the column names. Here's the query I am currently working with.

SELECT 
     * 
FROM 
    myTable 
UNPIVOT (
            val 
        FOR 
            column_name 
        IN (
               SELECT 
                   column_name 
               FROM 
                   USER_TAB_COLUMNS 
               WHERE 
                   table_name = 'myTable'
           )
        )

Should we assume that your desired results are to have 2 and 3 in the val column rather than repeating a val of 1 in 3 different rows? — Justin Cave
– Justin Cave, Commented Dec 11, 2012 at 19:11

Taryn · Accepted Answer · 2012-12-11 19:33:20Z

2

Unless I am missing something, why can't you use this. The UNPIVOT requires that all data be of the same type, so you mush cast data as needed:

select value, colName
from
(
  select to_char(col1) col1,
    col2
  from yourtable
) 
unpivot
(
  value
  for colName in (col1, col2)
) 
order by value

See SQL Fiddle with Demo

Result:

| VALUE | COLNAME |
-------------------
|     1 |    COL1 |
|     2 |    COL1 |
|     3 |    COL1 |
|     a |    COL2 |
|     b |    COL2 |
|     c |    COL2 |

edited Dec 11, 2012 at 19:33

answered Dec 11, 2012 at 19:14

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Over a year ago

The UNPIVOT requires that all data be of the same type this was my main problem. Thanks.

John Over a year ago

follow up question what are src and un?

Taryn Over a year ago

@johnthexiii they are aliases for the stuff inside the parentheses. They are not needed for Oracle. I am used to SQL Server syntax where it is required. I undated my solution.

Collectives™ on Stack Overflow

Joining column names to column values

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related