An old thread but, yes... there is a way to do it in Oracle:
with
employee(id, firstname, lastname, hobbies) as
(
select 1, 'a', 'b', '1' from dual union
select 2, 'a', 'b', '2' from dual union
select 3, 'a', 'b', '3' from dual union
select 4, 'c', 'd', '3' from dual union
select 5, 'e', 'f', '2' from dual
)
select *
from employee
pivot
(
max(1) fake
for (hobbies) -- put the undesired columns here
IN ()
)
where 1=1 -- and anything more...
order by id
What it does - the PIVOT - is group all rows through all columns except those declared in the PIVOT clause. The declared columns will be replaced by new columns defined by the aggregations before the FOR clause for each filter specified inside the IN clause.
A better example would be the following query:
select *
from employee
pivot
(
max(id) foo,
max(1) bar
for (hobbies)
IN ('2' as two, '3' as three)
)
That's the result:
FIRSTNAME | LASTNAME | TWO_FOO | TWO_BAR | THREE_FOO | THREE_BAR
c d null null 4 1
e f 5 1 null null
a b 2 1 3 1
All columns except the id and hobbies will be grouped, so it will group by firstname and lastname, which will result in three groups ('c', 'd') | ('e', 'f') | ('a', 'b'). Following, four columns will be created... two aggregation columns (as there are two MAX clause specified) for each filter in the IN clause and the aggregations are applied in the resulting groups.
Well, returning to the first query, it does works for two reasons:
1- you will not lose any row in the grouping process because the id column is unique and no columns were specified for aggregations;
2- as the pivot generates N * M new columns, where N = "number of filters" and M = "number of aggregations", having no filters and that single "harmless" aggregation means in 1 * 0 = 0 new columns, also removing all columns specified in the PIVOT clause, which is just the hobbies.