Stefan Oravec’s Blog

powermultiset and listagg () withing group ()

Uverejnené Február 9, 2011. Filed under: Uncategorized | Heslá:aggregate, sql, SQL Object Type |

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 ().

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.

Recently on Stefan Oravec's Blog…