create pivot in pl/sql

user10316500

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

Sven W.

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.

SomeoneElse

> Is there an other functionality in PL/SQL

To do what?  What are your requirements?

Nag Aswadhati

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

EdStevens

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. 

user10316500

These are 2 examples :

BluShadow

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.

user10316500

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):

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;

BluShadow

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.

EdStevens

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.

Sven W.

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.