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 () -- no values here...
)
where 1=1 -- and anythingyour morefilters here...
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 beforeTo understand how the FOR clause for each filter specified insidePIVOT works and why it solves the IN clause.
Aquestion, lets take a better example would be the following queryfor our employee
table sample:
That's theThe result here is:
All columns except the id and hobbies willThe exact same output can be grouped, so it will group by firstname and lastname, which will result in three groups ('c'achieved using this easier to understand query:
select
firstname,
lastname,
max(case when hobbies = '2' then id end) two_foo,
max(case when hobbies = '2' then 1 end) two_bar,
max(case when hobbies = '3' then id end) three_foo,
max(case when hobbies = '3' then 1 end) three_bar
from employee
group by
firstname,
lastname
So, 'd') |the column ('e'hobbies
is never selected, 'f') |just as the column ('a', 'b'). Followingid
, four columns will be created... two aggregation columns (as there are two MAX clauseboth specified) for each filter in inside the INPIVOT clause and the aggregations. All other columns are applied in the resulting groupsgrouped and selected.
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 idid column is unique and no columns were specified for aggregations;
2- as the pivot generates N * M new columns, where N = "numbernumber of filters"values of the IN clause and M = "numbernumber of aggregations"aggregations specified, so having no filters and that single "harmless"harmless aggregation means in 1 *will produce 0 * 1 = 0 new columns, also removing all columns and will remove the ones specified in the PIVOTPIVOT clause, which is just the hobbies.
The first line of this question says: "... without having to specify the fields you want". In all other positive answers the proposed queries specifies the desired fields in the SELECT clause, except in mine, actually.
- a list of undesired columns;
- an ININ clause;
- a three characters clause - FORFOR and NOTNOT;
It means that you need to write a bit more in my approach as you need a fake aggregation (you don't need to give it an alias as I did, just write MAX(1)) and the PIVOTPIVOT clause... It's too much for me if I spend two seconds to write these keywords and also, I have no problem to remember how to write this query - it is as easy to remember as the probable "standard". I can'tbut it's really see a writer's cramp herefew characters more...