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.


Make a Comment

Pridaj komentár

Zadajte svoje údaje, alebo kliknite na ikonu pre prihlásenie:

WordPress.com Logo

Na komentovanie používate váš WordPress.com účet. Odhlásiť sa / Zmeniť )

Twitter picture

Na komentovanie používate váš Twitter účet. Odhlásiť sa / Zmeniť )

Facebook photo

Na komentovanie používate váš Facebook účet. Odhlásiť sa / Zmeniť )

Google+ photo

Na komentovanie používate váš Google+ účet. Odhlásiť sa / Zmeniť )

Connecting to %s

  • Co prave robim

    • 【「HUNTER×HUNTER」コラボ開催!】#モンスト にゴン、キルア、クラピカ、レオリオ、ビスケが登場!今ならログインで「ハンターライセンス」がゲットできるよ!共に「三ツ星ハンター」を目指そう! #ハンターハンターモンスト xfl.ag/2zz1FXz 2 months ago
    • モンスト『 #4周年人気投票ガチャ 』 パーシヴァルに投票したよ! 欲しかったあのモンスターが、キミの1票で排出対象になるかも!? 皆も投票しよう! #モンスト知るかよ bit.ly/2wsmRct 3 months ago
    • 【モンスト4周年?感謝キャンペーンなんて知るかよ!】★6確定の2つの無料ガチャに、1000万円が最速翌日届くスピードくじ&予想的中で3億円山分け企画、エヴァンゲリオンや電撃25周年とのコラボ!?・・・もう知ってた? #モンスト xfl.ag/2xh8b3O 3 months ago
    • 【「七つの大罪 聖戦の予兆」コラボ開催!】メリオダス、ディアンヌ、バン、キング、ゴウセルがモンストに登場!ギルサンダー、ヘルブラム、ヘンドリクセン等がクエストに出現!伝説の騎士団<七つの大罪>と共に闘おう! #モンスト xfl.ag/2vTgQ8F 4 months ago
    • 【「#ナツの極み。」キャンペーン】海やプールで、夏の限定ガチャが無料で引ける「ナツ玉」がもらえる!?さらに、“運極”作るとサーティワンのアイスが99%当たる!モンストで、一緒に“ナツ”を極めよう! #モンスト xfl.ag/2vExw3Q 5 months ago
    • 【鋼の錬金術師 FAコラボ開催!】エルリック兄弟、ロイ・マスタング、リン・ヤオ、リザ・ホークアイがモンストにやってきた!スカー、エンヴィー、グリードがクエストに出現!“錬金術師”達と共に闘おう! #ハガレンモンスト #モンスト bit.ly/2sNPPBR 6 months ago
    • 映画『パイレーツ・オブ・カリビアン/最後の海賊』とのコラボ開催中!金と銀の“ジャック・スパロウ“をゲットで「パイレーツ缶」が当たるキャンペーンも!ジャック・スパロウと一緒にモンストで冒険しよう! #モンスト #パイレーツモンスト xfl.ag/2re67pF 6 months ago
    • 【リアル版 超・獣神祭『#十二支再競争』】本物のネズミやトラが走る「十二支伝説」のやり直し競争!?1着的中で投票したオーブの10倍のオーブGET!更に1&2着を的中で総額3億円分を山分け!どの動物に投票する? #モンスト xfl.ag/2r8817u 7 months ago
    • 「映画ドラえもん」コラボキャラを“運極”したいな!ミッションでキャラが入手しやすいからできるかも!レアなプレゼントが貰える「運極カチコチ大作戦キャペーン」もあるから、みんなも挑戦してみてね! #モンスト #モンストドラえもん xfl.ag/2lG0kab 10 months ago
  • Meta

  • Kategórie

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

%d bloggers like this: