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
