Oracle SQL: Transpose Columns to Rows

Question

There have been some questions on this but I couldn't find a relevant result for me. I have a query that gives me 2 columns of results:

 Day            Tot_dLS   
 01-Sep-12      10000
 02-Sep-12      9920

I want to transpose the Date so it reads:

01-Sep-12    02-Sep-12 
10000         9920

Is this possible?

Do you know at compile time that two rows (or any fixed number of rows) will be returned and that the two column names that you want are "01-Sep-12" and "02-Sep-12"? Do you need those column names or could you use generic column names? — Justin Cave, Sep 18 '12 at 17:46
http://www.club-oracle.com/forums/pivoting-row-to-column-conversion-techniques-sql-t144/ read all the way down — gloomy.penguin, Sep 18 '12 at 17:50
@JustinCave the above example was more generic, my actual results rows could have 10,20,30 distinct dates within.I wouldn't necessarily need the column names to read the date, to answer your question. — Americo, Sep 18 '12 at 17:55
The number of columns a SQL statement returns must be fixed at the time that the query is compiled. Is there some maximum number of columns that you could allow? If you need the number of columns to vary then you'd be looking at dynamically building the SQL statement every time you need to execute it so that the number of columns in the result is always correct. That adds quite a bit of complexity. — Justin Cave, Sep 18 '12 at 17:58
I could do, say, 30 columns, but I would need to change the date range constraints on the query as time progressed. Is @Andrea Bergia on to something with his answer? — Americo, Sep 18 '12 at 18:00
possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — Justin Cave, Sep 18 '12 at 18:08

score 1 · Answer 1 · answered Sep 18 '12 at 17:49

1

Not really with a SQL query, since the same column would have to contain two different data types. You can get by with some tricks (casting everything to string) - but such things are much better done in the presentation application, or report, itself, than from the query.

answered Sep 18 '12 at 17:49

Andrea Bergia

5,502
1
23
38

is there a repository to learn more about this strategy? Because if I have more than a few results rows, it seems like the other strategies can get redundant and time consuming. Thanks! – Americo Sep 18 '12 at 17:54

score 1 · Accepted Answer · answered Sep 18 '12 at 18:07

Since you can return a fixed number of columns and you can use generic column names, you can do a standard pivot query

SELECT max( case when rn = 1 then tot_dls else null end ) col_1,
       max( case when rn = 2 then tot_dls else null end ) col_2,
       max( case when rn = 3 then tot_dls else null end ) col_3,
       <<25 more>>
       max( case when rn = 29 then tot_dls else null end ) col_29,
       max( case when rn = 30 then tot_dls else null end ) col_30
  FROM (SELECT day,
               tot_dls,
               rank() over (order by day) rn
          FROM your_table
         WHERE day between date '2012-09-01' 
                       and date '2012-09-02' -- Use whatever criteria you want here
       )

where is says <<25 more>> does that require my manual entry? It doesn't seem to query as is. — Americo, Sep 18 '12 at 18:25
@Stuav - Yes. I left off the 25 lines where you would generate `col_4` through `col_29` in exactly the same way that I did `col_1` through `col_3`. You would need to explicitly list those, I left them off to avoid cluttering up my answer. — Justin Cave, Sep 18 '12 at 18:27

Tai Paul · Answer 3 · 2012-10-16T15:07:59.547

This code:

create table your_table(day date, tot_dls number(5));
insert into your_table values ('01-SEP-2012',10000);
insert into your_table values ('02-SEP-2012',9920);
insert into your_table values ('03-SEP-2012',12020);
insert into your_table values ('04-SEP-2012',11030);

column dummy noprint
column "Header" format a7
column "Data"   format a60
set hea off
SELECT 0 DUMMY
      ,'Day'                                      "Header"
      ,LISTAGG(' ' || TO_CHAR(Day,'DD-MON-YYYY')) WITHIN GROUP (ORDER BY Day) "Data"
FROM  your_table
UNION
SELECT 1
      ,'Tot_dls'
      ,LISTAGG(LPAD(TOT_DLS,13-LENGTH(TO_CHAR(TOT_DLS,'FM')),' ')) WITHIN GROUP (Order by Day)
FROM your_table
ORDER by 1;

produces this output using SQL*Plus on an Oracle 11g (11.2.0) database.

Day      01-SEP-2012 02-SEP-2012 03-SEP-2012 04-SEP-2012
Tot_dls        10000        9920       12020       11030

Taryn · Answer 4 · 2012-09-18T18:12:48.583

0

You can use CASE statements and an aggregate to perform this. You can use something like this:

select max(case when day = '01-Sep-12' then Tot_dLS end) "01-Sep-12",
       max(case when day = '02-Sep-12' then Tot_dLS end) "02-Sep-12",
       ........ add more columns here
from yourtable

then this would be expanded on to add more columns.

edited Sep 18 '12 at 18:12

answered Sep 18 '12 at 17:47

Taryn

242,637
56
362
405

Oracle SQL: Transpose Columns to Rows

4 Answers4