powermultiset and listagg () withing group ()

Posted on február 9, 2011. Filed under: Uncategorized | Značky:, , |

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');


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

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

  • Co prave robim

    • 【「七つの大罪 聖戦の予兆」コラボ開催!】メリオダス、ディアンヌ、バン、キング、ゴウセルがモンストに登場!ギルサンダー、ヘルブラム、ヘンドリクセン等がクエストに出現!伝説の騎士団<七つの大罪>と共に闘おう! #モンスト xfl.ag/2vTgQ8F 1 day ago
    • 【「#ナツの極み。」キャンペーン】海やプールで、夏の限定ガチャが無料で引ける「ナツ玉」がもらえる!?さらに、“運極”作るとサーティワンのアイスが99%当たる!モンストで、一緒に“ナツ”を極めよう! #モンスト xfl.ag/2vExw3Q 1 month ago
    • 【鋼の錬金術師 FAコラボ開催!】エルリック兄弟、ロイ・マスタング、リン・ヤオ、リザ・ホークアイがモンストにやってきた!スカー、エンヴィー、グリードがクエストに出現!“錬金術師”達と共に闘おう! #ハガレンモンスト #モンスト bit.ly/2sNPPBR 2 months ago
    • 映画『パイレーツ・オブ・カリビアン/最後の海賊』とのコラボ開催中!金と銀の“ジャック・スパロウ“をゲットで「パイレーツ缶」が当たるキャンペーンも!ジャック・スパロウと一緒にモンストで冒険しよう! #モンスト #パイレーツモンスト xfl.ag/2re67pF 2 months ago
    • 【リアル版 超・獣神祭『#十二支再競争』】本物のネズミやトラが走る「十二支伝説」のやり直し競争!?1着的中で投票したオーブの10倍のオーブGET!更に1&2着を的中で総額3億円分を山分け!どの動物に投票する? #モンスト xfl.ag/2r8817u 2 months ago
    • 「映画ドラえもん」コラボキャラを“運極”したいな!ミッションでキャラが入手しやすいからできるかも!レアなプレゼントが貰える「運極カチコチ大作戦キャペーン」もあるから、みんなも挑戦してみてね! #モンスト #モンストドラえもん xfl.ag/2lG0kab 6 months ago
    • 年末年始もモンスト!大晦日に「年末BIGボーナスくじ」でオーブ5個届く&総額1億円が当たる!大晦日と元旦はログインでオーブ合計20個!元旦より超・獣神祭の限定新キャラ「パンドラ」登場! #モンストやるなよ #モンスト xfl.ag/2fKD1ot 8 months ago
    • 「幽☆遊☆白書」コラボ開催!モンストに浦飯幽助、蔵馬、飛影、桑原和真、幻海が登場!登場キャラボイスは全て録りおろし!”伊達にあの世は見てねぇぜ!”幽助達と一緒に「暗黒武術会」に挑め! #モンスト #幽白モンスト xfl.ag/2fcrlwu 10 months ago
  • Meta

  • Kategórie

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