powermultiset and listagg () withing group ()

Pridané dňa Február 9, 2011. Zatriediť pod: Uncategorized | Heslá:, , |

I need to create simple “dimension table” for reporting application I currently work on. Problem is that dimension is a “bit ugly”. Application is storing m:n relation Animal:BreedingType in Animal’s column as delimited string.

So I need to dynamically populate this dimension from enum definition with all possible delimited strings of codes and descriptions in my languages.

Sample table – BreedingType enum.


create table DE_BreedingType
(
  Code varchar2 (30 byte)  not null ,
  Description_TR varchar2 (255 byte) ,
  Description_EN varchar2 (255 byte)
);

alter table DE_BreedingType
  add constraint DE_BreedingType_PK
  primary key ( Code ) ;

insert into de_breedingtype values ('I', 'Süt', 'Milk');
insert into de_breedingtype values ('M', 'Et', 'Meat');
insert into de_breedingtype values ('W', 'Yün', 'Wool');
insert into de_breedingtype values ('B', 'Yetiştirme', 'Breeding');

commit;

I could do it with some PL/SQL but SQL is much elegant and simple. I will use PowerMultiSet function, which returns all submultisets for given multiset – in my case all collections of sub-collections of enums.

I have to create SQL Object Types first.


--enum object
create or replace type taro_enum as object
(
  Code           varchar2(30),
  Description_TR varchar2(255),
  Description_EN varchar2(255)
);
--table of objects
create type tart_enum as table of taro_enum;
--table of tables of objects
create type tartt_enum as table of tart_enum;

Now I can select all sub-multisets.


--1. collect objects and cast colection as table of objects
--2. compute powermultiset
--3. cast resulting collection as table of tables of objects
select cast(powermultiset(cast(collect(taro_enum(Code           => Code,
                                                 Description_TR => Description_TR,
                                                 Description_EN => Description_EN)) as tart_enum)) as
            tartt_Enum)
  from de_breedingtype;

Now I’m almost there. I “just” need to concatenate rows in in each submultiset. It’s simple and elegant in 11g using listagg () within group ().

In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function

Thanks to Don


select (select listagg(Code, ',') within group( order by Code) 
          from table(column_value)) as Code
      ,(select listagg(Description_EN, ',') within group( order by Code)
          from table(column_value)) as Description_EN
      ,(select listagg(Description_TR, ',') within group( order by Code) Code
          from table(column_value)) as Description_TR
  from table (select cast(powermultiset(
                       cast(collect(taro_enum(Code           => Code,
                                              Description_TR => Description_TR,
                                              Description_EN => Description_EN))
                            as tart_enum))
                          as tartt_Enum)
                from de_breedingtype);

CODE       DESCRIPTION_EN             DESCRIPTION_TR
---------- -------------------------- --------------------------
I          Milk                       Süt
M          Meat                       Et
I,M        Milk,Meat                  Süt,Et
W          Wool                       Yün
I,W        Milk,Wool                  Süt,Yün
M,W        Meat,Wool                  Et,Yün
I,M,W      Milk,Meat,Wool             Süt,Et,Yün
B          Breeding                   Yetistirme
B,I        Breeding,Milk              Yetistirme,Süt
B,M        Breeding,Meat              Yetistirme,Et
B,I,M      Breeding,Milk,Meat         Yetistirme,Süt,Et
B,W        Breeding,Wool              Yetistirme,Yün
B,I,W      Breeding,Milk,Wool         Yetistirme,Süt,Yün
B,M,W      Breeding,Meat,Wool         Yetistirme,Et,Yün
B,I,M,W    Breeding,Milk,Meat,Wool    Yetistirme,Süt,Et,Yün

And that’s about it.


Read Full Post | Make a Comment ( None so far )

Recently on Stefan Oravec's Blog…

Oracle SQL Developer Data Modeler 3.0 production – migrating Domains from EA (Mac OS X)

Pridané dňa Február 1, 2011. Zatriediť pod: Uncategorized | Heslá:, |

Back on Track

Pridané dňa Január 28, 2011. Zatriediť pod: Uncategorized |

No sum for interval day to second?

Pridané dňa Október 30, 2009. Zatriediť pod: Uncategorized | Heslá:, , , , |

Is your physical model “XML ready”?

Pridané dňa Október 26, 2009. Zatriediť pod: Uncategorized | Heslá:, |

PL/SQL try-catch-finally concept

Pridané dňa Október 9, 2009. Zatriediť pod: Uncategorized | Heslá:, |

Oracle SQL Developer Release 2.1 – Early Adopter 1

Pridané dňa Október 5, 2009. Zatriediť pod: Uncategorized | Heslá: |

zrada materialized view log-ov

Pridané dňa Jún 16, 2009. Zatriediť pod: Uncategorized | Heslá:, , |

user_views_definition

Pridané dňa Jún 4, 2009. Zatriediť pod: Uncategorized | Heslá:, , |

a je to tu!

Pridané dňa Máj 16, 2009. Zatriediť pod: Uncategorized |

Liked it here?
Why not try sites on the blogroll...

Sleduj

Prijímaj upozornenia na nové články na tvoj email.