oracle11g - Oracle listagg query -


this query result:

weekenddate ccname phname ratio 08-feb-15 apple line 1 - day l&i work 0.45 08-feb-15 apple line 1 - day sorter 6.85 08-feb-15 apple line 1 - day tray fill 12.93 08-feb-15 apple line 1 - day wh general labor 5.6 08-feb-15 apple line 1 - day wh supervisor 1.48 15-feb-15 apple line 1 - day l&i work 0.42 15-feb-15 apple line 1 - day sorter 6.09 15-feb-15 apple line 1 - day tray fill 11.9 15-feb-15 apple line 1 - day wh general labor 5.42 15-feb-15 apple line 1 - day wh supervisor 3.46 22-feb-15 apple line 1 - day l&i work 0.43 22-feb-15 apple line 1 - day sorter 6.01 22-feb-15 apple line 1 - day tray fill 12.09 22-feb-15 apple line 1 - day wh general labor 4.9 22-feb-15 apple line 1 - day wh supervisor 1.71

my boss wants data sideways.
is there way query result formatted this:

ccname phname 08-feb-15 15-feb-15 22-feb-15 apple line 1 - day l&i work 0.45 0.42 0.43 apple line 1 - day sorter 6.85 6.09 6.01 apple line 1 - day tray fill 12.93 11.9 12.09 apple line 1 - day wh general labor 5.6 5.42 4.9 apple line 1 - day wh supervisor 1.48 3.46 1.71

or this:

ccname phname 08-feb-15,15-feb-15,22-feb-15 apple line 1 - day l&i work .45,.42,.43 apple line 1 - day sorter 6.85,6.09,6.01 apple line 1 - day tray fill 12.93,11.9,12.09 apple line 1 - day wh general labor 5.6,5.42,4.9 apple line 1 - day wh supervisor 1.48,3.46,1.71

here query able figure out:
please note i not know date range in advance.
depends on how many week(s) user asks for.

select ccname, phname, listagg(weekenddate||','||ratio,'; ')  within group(order ccname, phname) ratio (...leaving out code...) group ccname, phname 

but result below not quite enough: can use 'listagg' or should researching 'pivot' instead?

ccname phname ratio apple line 1 - day l&i work 08-feb-15,.45;15-feb-15,.42;22-feb-15,.43 apple line 1 - day sorter 08-feb-15,6.85;15-feb-15,6.09;22-feb-15,6.01 apple line 1 - day tray fill 08-feb-15,12.93;15-feb-15,11.9;22-feb-15,12.09 apple line 1 - day wh general labor 08-feb-15,5.6;15-feb-15,5.42;22-feb-15,4.9 apple line 1 - day wh supervisor 08-feb-15,1.48;15-feb-15,3.46;22-feb-15,1.71


Popular posts from this blog