I need an sql select statement that would transform
1
2
3
4
into 1 2 3 4
Without using PIVOT functions as I am running oracle 10 database. Searched quite a lot on the Internet but surprisingly found nothing.
Little help?
Add a comment
|
1 Answer
You can do a pivot almost as efficiently using a cross join:
select max(case when seqnum = 1 then t.col end) as col1,
max(case when seqnum = 2 then t.col end) as col2,
max(case when seqnum = 3 then t.col end) as col3,
max(case when seqnum = 4 then t.col end) as col4
from (select t.*, row_number() over (order by NULL) as seqnum
from t
) t
4 Comments
Denys
Thanks for your answer. Do you know a solution for the same problem if the number of columns is not limited to 4? Because in my problem - there are 6 of them and i imagine that I could have come across even higher number of columns
Gordon Linoff
@Dennis . . . A SQL select query has to have a fixed number columns. You can add additional clauses in the
select for addition columns. But you cannot vary the number (they will be populated with NULL if there is no value). Alternatively, you could use list_agg to put them all in one column, with delimiters.Denys
Right, I see. Thanks a lot. I'll jot this solution down, as it seems to be a trivial task.
Denys
Either Im doing something wrong or this query returns the row filled with only max values from the column