Forum Stats

  • 3,873,118 Users
  • 2,266,506 Discussions
  • 7,911,426 Comments

Discussions

Categories

create pivot in pl/sql

user10316500
user10316500 Member Posts: 38 Blue Ribbon
edited Dec 12, 2017 9:04AM in SQL & PL/SQL

Hi

I tried using PIVOT functionality in PL/SQL 11g but it's too limited for my queries.

I have to create complex queries.

At the moment I use sum(decode(...),rollup, grouping ecc.... to pivot columns.

Is there an other functionality in PL/SQL or a Java Class to call from PL/SQL (CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED....) ?

Stefano

Tagged:
Frank KulashBluShadow
· Share on TwitterShare on Facebook

Best Answer

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Nov 20, 2015 9:01AM Answer ✓

    Your task simply seems to involve a matrix like visualization of data.

    SQL is very strong at data processing. However it is not the best "language" to do visualizations.

    I suggest you rethink your application modules.

    Let SQL fetch the data in a normalized way.

    Then use some kind of reporting tool (Apex, Cognos, Excel, ... ) to visualize the data in a matrix.

    BluShadow
    · Share on TwitterShare on Facebook

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,867 Silver Crown
    edited Nov 19, 2015 11:57AM

    > Is there an other functionality in PL/SQL

    To do what?  What are your requirements?

    · Share on TwitterShare on Facebook
  • Nag Aswadhati
    Nag Aswadhati Member Posts: 144
    edited Nov 19, 2015 11:57AM

    Please provide your queries and code where you are facing exact problem.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Nov 19, 2015 11:58AM 
    user10316500 wrote:

Hi

I tried using PIVOT functionality in PL/SQL 11g but it's too limited for my queries.

    Maybe, maybe not.  We can't know until we see your tables, sample data, and actual requirements.

    And PIVOT is a SQL option.  Not PL/SQL. 

    · Share on TwitterShare on Facebook
  • user10316500
    user10316500 Member Posts: 38 Blue Ribbon
    edited Nov 20, 2015 3:47AM

    These are 2 examples :

    1.png

    2.png

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,549 Red Diamond
    edited Nov 20, 2015 4:31AM

    Please read:

    and post some example data in a format we can use to demonstrate a solution (e.g. create table and insert statements)

    Having a screen dump of a load of figures without any explanation or logic is no use to anybody.

    · Share on TwitterShare on Facebook
  • user10316500
    user10316500 Member Posts: 38 Blue Ribbon
    edited Nov 20, 2015 4:41AM

    There are many tables: it's difficult for me describing all tables.


    I'm searching only an alternative (if exists).


    At the moment I did a query only for this table (for the 2 tables above no):

    3.png

    with vista

       as (select /*+ parallel(cs01,4) materialize*/

      *

       from hub.report_cs01_condiviso cs01

      where 1 = 1

      and id_messaggio is not null

      and (case

       when cs01.flusso_scarto is null

       then

       'Verifica HUB'

       when cs01.flusso_scarto like 'PEAT%'

       then

       'Letture Peat'

       when (cs01.flusso_scarto like 'CONSUMI_GEE_LC_T500%')

      or (cs01.flusso_scarto like 'LETTURE_EE_T510%'

      and cs01.processo_etl is null)

       then

       'Letture'

       else

       'Processi'

       end) = 'Processi')

      select /*+ cardinality(tb 100000) leading(tb) */

       case

      when trim (tb.stream) = 'CS01 FA' then '[FA]  - Fuori Ambito'

      else case when tbs.ambito is not null then '[CFA] - Candidato Fuori Ambito' else '[IA]  - In Ambito' end

      end

      kpi_ambito_reale,

      sum (decode (tb.owner, 'ACN', 1, 0)) as ACN,

      sum (decode (tb.owner, 'ACN<HUB', 1, 0)) as ACN_MIN_HUB,

      sum (decode (tb.owner, 'ENG<ETL', 1, 0)) as ENG_MIN_ETL,

      sum (decode (tb.owner, 'ENG<ETL<ACN', 1, 0)) as ENG_MIN_ETL_MIN_ACN,

      sum (decode (tb.owner, 'ETL', 1, 0)) as ETL,

      sum (decode (tb.owner, 'ETL<HUB', 1, 0)) as ETL_MIN_HUB,

      sum (decode (tb.owner, 'HUB<ETL', 1, 0)) as HUB_MIN_ETL,

      sum (decode (tb.owner, null, 1, 0)) as "NULL",

      --

      sum (decode (tb.owner, 'ACN', 1, 0)) +

      sum (decode (tb.owner, 'ACN<HUB', 1, 0)) +

      sum (decode (tb.owner, 'ENG<ETL', 1, 0)) +

      sum (decode (tb.owner, 'ENG<ETL<ACN', 1, 0)) +

      sum (decode (tb.owner, 'ETL', 1, 0)) +

      sum (decode (tb.owner, 'ETL<HUB', 1, 0)) +

      sum (decode (tb.owner, 'HUB<ETL', 1, 0)) +

      sum (decode (tb.owner, null, 1, 0))

      as TOTALE_COMPLESSIVO

      from vista tb,

      eni_it_tmp_cs01_problem_999 tbs,

      (select /*+ parallel(dtsc,4) use_nl(dtsc tdsk ana)*/

      dtsc.id_messaggio,

       ana.*,

       tdsk.tdsk_data_invio_mail_sfac,

       tdsk.tdsk_data_invio_mail_eni,

       tdsk.tdsk_problem_id

      from eni_tavolo_etl_dett dtsc, ENI_TAVOLO_DET_REL_ANA tdsk, ENI_TAVOLO_ETL_ANALISI ana

       where dtsc.dtsc_tavolo_dett_id = tdsk.dtsc_tavolo_dett_id(+)

      and tdsk.tan_tavolo_analisi_id = ana.tan_tavolo_analisi_id(+)

      and dtsc.dtsc_tavolo_dett_id = dtsc.dtsc_storico_id

      and dtsc.dtsc_data_fin = to_timestamp ('31-DIC-9999 00:00:00')

      and dtsc.dtsc_flag_ann = 'N') tdd,

      eni_crusc_utenti uo

      where tbs.id_messaggio(+) = tb.id_messaggio

       and tdd.id_messaggio(+) = tb.id_messaggio

       and uo.ecu_utente_id(+) = tdd.tan_creator_owner

    group by rollup (case

       when trim (tb.stream) = 'CS01 FA' then '[FA]  - Fuori Ambito'

       else case when tbs.ambito is not null then '[CFA] - Candidato Fuori Ambito' else '[IA]  - In Ambito' end

       end)

    order by 1 nulls last;

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,549 Red Diamond
    edited Nov 20, 2015 4:51AM

    Well, that's fine giving us the query you're currently using, but we need tables to run it against.

    How can anybody understand your issue and be able to write code for you to solve it, if they cannot run your current query.

    We don't have your tables.  We don't have your data (we only need a sample to illustrate the issue).  We don't have your knowledge of what you are trying to achieve.

    The best way to get answers to your issues is to provide people will an example of what the issue is, explaining the logic you want, and supplying some example data that people can use to replicate the issue and thus provide solutions.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Nov 20, 2015 8:17AM 
    user10316500 wrote:

There are many tables: it's difficult for me describing all tables.

    We don't need all of your tables.  Just enough to demonstrate the general problem and general solution.

    Frank Kulash
    · Share on TwitterShare on Facebook
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Nov 20, 2015 9:01AM Answer ✓

    Your task simply seems to involve a matrix like visualization of data.

    SQL is very strong at data processing. However it is not the best "language" to do visualizations.

    I suggest you rethink your application modules.

    Let SQL fetch the data in a normalized way.

    Then use some kind of reporting tool (Apex, Cognos, Excel, ... ) to visualize the data in a matrix.

    BluShadow
    · Share on TwitterShare on Facebook
This discussion has been closed.