Oracle SQL Transpose

Question

Before I begin, I know there is a whole bunch of questions on Stackoverflow on this topic but I could not find any of them relevant to my case because they involve something much more complicated than what I need.

What I want is a simple dumb transpose with no logic involved.

Here is the original table that my select query returns:

Name Age Sex DOB Col1 Col2 Col3 ....  
A    12  M   8/7 aa   bb   cc

Typically, this is going to contain only 1 record i.e. for one person

Now what I want is

Field   Value
Name    A
Age     12
Sex     M
DOB     8/7
Col1    aa
Col2    bb
Col3    cc
.
.

So there is no counting, summing or any complicated logic involved like most of the similar question on Stackoverflow.

How do I do it? I read through the PIVOT and UNPIVOT help and it was not that helpful at all.

PS: By chance, if it contains more than one records, is it possible to return each record as a field somewhat like

Field   Value1   Value2   Value3 ...
Name    A        B        C      ...
Age     ..       ..       ..     ...
.
.

I want to know how to to do this for Oracle 10g and 11g

PS:Feel free to tag as duplicate if you find a question that is truly similar to mine.

Answer 1

I would suggest applying the UNPIVOT function first to your multiple columns, then using row_number() to create your new column names that will be used in the PIVOT.

The basic syntax for the unpivot will be

select field, 
  value,
  'value'||
   to_char(row_number() over(partition by field
                              order by value)) seq
from yourtable
unpivot
(
  value
  for field in (Name, Age, Sex, DOB, col1, col2, col3)
) u;

See SQL Fiddle with Demo. This is going to convert your multiple columns of data into multiple rows. I used row_number() to create a unique value for your new column names, the data from this query looks like:

| FIELD |                   VALUE |    SEQ |
|-------|-------------------------|--------|
|   AGE |                      12 | value1 |
|   AGE |                      15 | value2 |
|  COL1 |                      aa | value1 |
|  COL1 |                      xx | value2 |

Then you can apply the PIVOT function to this result:

select field, value1, value2
from
(
  select field, 
    value,
    'value'||
      to_char(row_number() over(partition by field
                                order by value)) seq
  from yourtable
  unpivot
  (
    value
    for field in (Name, Age, Sex, DOB, col1, col2, col3)
  ) u
) d
pivot
(
  max(value)
  for seq in ('value1' as value1, 'value2' as value2)
) piv

See SQL Fiddle with Demo. This gives a final result:

| FIELD |                  VALUE1 |                  VALUE2 |
|-------|-------------------------|-------------------------|
|   AGE |                      12 |                      15 |
|  COL1 |                      aa |                      xx |
|  COL2 |                      bb |                      yy |
|  COL3 |                      cc |                      zz |
|   DOB | 07-Aug-2001 12:00:00 AM | 26-Aug-2001 12:00:00 AM |
|  NAME |                       A |                       B |
|   SEX |                       F |                       M |

Note, when you are applying the unpivot function the datatype of all of the columns must be the same so you might have to convert your data in a subquery before you can unpivot it.

The UNPIVOT/PIVOT function were introduced in Oracle 11g, if you are using Oracle 10g, then you can edit the query to use:

with cte as
(
  select 'name' field, name value
  from yourtable
  union all
  select 'Age' field, Age value
  from yourtable
  union all
  select 'Sex' field, Sex value
  from yourtable
  union all
  select 'DOB' field, DOB value
  from yourtable
  union all
  select 'col1' field, col1 value
  from yourtable
  union all
  select 'col2' field, col2 value
  from yourtable
  union all
  select 'col3' field, col3 value
  from yourtable
)
select
  field,
  max(case when seq = 'value1' then value end) value1,
  max(case when seq = 'value2' then value end) value2
from
(
  select field, value,
  'value'||
      to_char(row_number() over(partition by field
                                order by value)) seq
  from cte
) d
group by field;

See SQL Fiddle with Demo