Doubt with Pivot

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 01 Mar 2018 2:47 pm

Good gentlemen, picking up to do a pivot.

I have the following select: select Department, dt_dia, value from table where month = ' 01/2018 ' results dept value date dep1 01/2018.10 .01/.02/01/2018.5 dep1 dep4 .01/01/2018.10 would like to pivotear where the date would turn column and the value was the return of this column.
I need the days as column. Dry example d1 d2 1 Department dep1 dep4 10 5 2 10 0
xprata
Location: São Paulo - SP

Poston Thu, 01 Mar 2018 3:07 pm

Hey xprata, take a look at the following link (noting that your bank 11 or higher): http://www.oracle.com/technetwork/pt/ar ... 3-ptb.html If you need, look at the following topic that useful information: viewtopic.php?f=3&t=10571&p=41562#p41562
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Thu, 01 Mar 2018 3:49 pm

Thanks for the retorno...as directions helped.
Now as I have to show every day, I had to do not include the query daily, both in presentation and in the pivot select xxx ... d01, d02, d03 .... d031 .... pivot max (value) is (dt) in (' 01/01/2018 ', ' 1/2/2018 ' d01 ... 31/01/2018 d31 ...

Do a loop to avoid the rewrite? and também has the case you change month ... dai have move on query q will clear?
xprata
Location: São Paulo - SP

Poston Thu, 01 Mar 2018 4:05 pm

Man, tries to make the pivot on the top of the field DAY but in your query you will have the month and year fields. So you only need to write the 31 days of the month.
Otherwise you will end up having a table with columns for 365 every day of the year. Is infeasible.

Code: Select all
select extract(day from sysdate) dia_x
     , to_char(sysdate, 'dd')    dia_char
     , sysdate
  from dual;
Now if you NEED it to be dynamic, without having to write days, etc., see the end of the above topic.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Fri, 02 Mar 2018 10:38 am

Thanks ... what will vary even the month ... I'll fix the days I'm going to do the tests with your tip ... t +
xprata
Location: São Paulo - SP



Return to PL/SQL

Who is online

Users browsing this forum: Google [Bot] and 5 guests