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
– Justin Cave, Commented Sep 18, 2012 at 17:46
@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
– Americo, Commented Sep 18, 2012 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
– Justin Cave, Commented Sep 18, 2012 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
– Americo, Commented Sep 18, 2012 at 18:00

Justin Cave · Accepted Answer · 2012-09-18 18:07:53Z

1

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
       )

answered Sep 18, 2012 at 18:07

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Americo Over a year ago

where is says <<25 more>> does that require my manual entry? It doesn't seem to query as is.

Justin Cave Over a year ago

@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.

Andrea Bergia · Accepted Answer · 2012-09-18 17:49:21Z

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, 2012 at 17:49

Andrea Bergia

5,5502 gold badges27 silver badges40 bronze badges

1 Comment

Americo Over a year ago

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!

Tai Paul · Accepted Answer · 2012-10-16 15:07:59Z

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 · Accepted Answer · 2012-09-18 18:12:48Z

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, 2012 at 18:12

answered Sep 18, 2012 at 17:47

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: Transpose Columns to Rows

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related