oracle - select to get null value rows for concatination -

-

here table (table_1)

select * table_1;  pk_1 pk_2 pk_3 pk_4 pk_5 col_1 col_2 col_3 ---- ---- ---- ---- ---- ----- ----- ----- aaa  bbb  ccc  ddd  1    1     2     3     aaa  bbb  ccc  ddd  2    4     5     6     aaa  bbb  ccc  ddd  3    7     8     9

i want output this:

pk_1 pk_2 pk_3 pk_4 aggregate ---- ---- ---- ---- ------------------- aaa  bbb  ccc  ddd  123 456 789 000 000

so have tried far:

select      pk_1 ,pk_2 ,pk_3 ,pk_4 ,     listagg (col_1 || col_2 || col_3 , ' ')within group (order pk_1 ,pk_2 ,pk_3 ,pk_4 , pk_5) "aggregate"      table_1 group     pk_1 ,pk_2 ,pk_3 ,pk_4 ;  pk_1 pk_2 pk_3 pk_4 aggregate ---- ---- ---- ---- ----------- aaa  bbb  ccc  ddd  123 456 789

the problem not getting 000 000 output because data missing 4th , 5th rows. need query output aggregate column in format xxx xxx xxx xxx xxx having 000 if respective rows missing according pk_5.

help please.. thinking needs with clause have no clue implement.

assuming expecting max of 5 rows per (pk_1, pk_2, pk_3, pk_4), following (which uses partition outer join) should trick:

col aggregate format a20;  table_1 (select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 1 pk_5, 1 col_1, 2 col_2, 3 col_3 dual union                  select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 2 pk_5, 4 col_1, 5 col_2, 6 col_3 dual union                  select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 3 pk_5, 7 col_1, 8 col_2, 9 col_3 dual),                  -- end of mimicking table table_1; wouldn't need subquery have table.        dummy (select level id                    dual                  connect level <= 5) select   pk_1,          pk_2,          pk_3,          pk_4,          listagg (nvl(col_1, 0)||nvl(col_2, 0)||nvl(col_3, 0), ' ') within group (order pk_1, pk_2, pk_3, pk_4, pk_5) aggregate   dummy d        left outer join table_1 t1 partition (t1.pk_1, t1.pk_2, t1.pk_3, t1.pk_4) on (t1.pk_5 = d.id) group pk_1,          pk_2,          pk_3,          pk_4;  pk_1 pk_2 pk_3 pk_4 aggregate            ---- ---- ---- ---- -------------------- aaa  bbb  ccc  ddd  123 456 789 000 000

n.b. if pk_5 column doesn't start numbering rows 1 each (pk_1, pk_2, pk_3, pk_4) you'll need use row_number() analytic function generate list of numbers join against dummy table:

with table_1 (select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 9 pk_5, 1 col_1, 2 col_2, 3 col_3 dual union                  select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 10 pk_5, 4 col_1, 5 col_2, 6 col_3 dual union                  select 'aaa' pk_1, 'bbb' pk_2, 'ccc' pk_3, 'ddd' pk_4, 11 pk_5, 7 col_1, 8 col_2, 9 col_3 dual),                  -- end of mimicking table table_1; wouldn't need subquery have table.        dummy (select level id                    dual                  connect level <= 5),           t1 (select pk_1,                         pk_2,                         pk_3,                         pk_4,                         row_number() on (partition pk_1, pk_2, pk_3, pk_4 order pk_5) pk_5,                         col_1,                         col_2,                         col_3                    table_1) select   pk_1,          pk_2,          pk_3,          pk_4,          listagg (nvl(col_1, 0)||nvl(col_2, 0)||nvl(col_3, 0), ' ') within group (order pk_1, pk_2, pk_3, pk_4, pk_5) aggregate   dummy d        left outer join t1 partition (t1.pk_1, t1.pk_2, t1.pk_3, t1.pk_4) on (t1.pk_5 = d.id) group pk_1,          pk_2,          pk_3,          pk_4;  pk_1 pk_2 pk_3 pk_4 aggregate            ---- ---- ---- ---- -------------------- aaa  bbb  ccc  ddd  123 456 789 000 000

Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more