powermultiset and listagg () withing group ()
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
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 )