Skip to Main Content
  • Questions

  • Custom pivot with count and sum summaries and horizontal sorting

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kaushal.

Asked: April 06, 2018 - 10:48 am UTC

Last updated: July 25, 2018 - 1:22 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello Team,

Good Day!

I have linked livesql script for data creation. Data basically looks like this.

1 symnum NUMBER  22   
2 symname VARCHAR2   100  
3 remnum NUMBER  22   
4 remname VARCHAR2  32   
5 grade NUMBER  22



symnum Symname            remnum remname grade
59 Eyes, Vision, Hypermetropia (hyperopia, longsightedness) 3 Alum 1
59 Eyes, Vision, Hypermetropia (hyperopia, longsightedness) 5 Amm-c 1
59 Eyes, Vision, Hypermetropia (hyperopia, longsightedness) 18 Bell 2
59 Eyes, Vision, Hypermetropia (hyperopia, longsightedness) 22 Bry  1



I want custom pivot table with fixed 126 columns, but varying number of rows based on filter on Symname. Another important thing is that I want to have two additional rows :
1) Count where grade is not null for a remname
2) Sum of grade.

Below is a specimen of the output which is expected (with truncated number of columns)

Symname                Nux-v   Bell  Phos Sulf Rhus Puls Staph
Blood & Circulation, Haemorrhage (bleeding), from inner parts   4    4   4     4     3     4     1
Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)   4    4   4     3     4     3     2
Modalities, situation & circumstances, Rising,                  4    4   3     4     4     3     2
Modalities, situation & circumstances, Teeth, cleaning, from                                3
Teeth & Gums, Gums                                               4    3   3     2     2     2     4
Grand Total                                                      16   15  14    13    13    12    12
count                                                           4    4   4     4     4     4     5


Now as per the requirement, Column staph should be listed after symname as the count is 5 which is greater than all the other columns (horizontal sorting). Sort on count + Grand Total is required.

I feel model clause could help here but I am not familiar with it. Kindly help.

PS. I reviewed the question and the formatting seems to be messed up even with code tags. Is there any option for table or image post to show the specimen?

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/gifrhfpqusimyi856s7y53xa0

and Chris said...

OK.

What you could do is:

- Get the totals for each (remname, symname) and remname using grouping sets
- For the "grand totals", assign a row number for your desired ordering
- Use min to set this number for all other rows for the same remname

At this point you have assigned a number (1 - N) for each remname you want to display. So you can pivot by these numbers to get the results in the order you want:

with totals as (
  select "symname" sym, "remname" rm, sum("grade") sm, count("symname") ct
  from   remsym
  where  "remname" in ( 'Nux-v', 'Bell', 'Phos', 'Sulf', 'Rhus', 'Puls', 'Staph' )
  and    "symname" in (
    'Blood & Circulation, Haemorrhage (bleeding), from inner parts',
    'Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)',
    'Modalities, situation & circumstances, Rising, on (on straightening-up, becoming erect)',
    'Modalities, situation & circumstances, Teeth, cleaning, from',
    'Teeth & Gums, Gums'
  )
  group by grouping sets ( ("remname", "symname"), "remname") 
), rankings as (
  select t.*,
         case 
           when sym is null then row_number() over (partition by sym order by sm desc, ct) 
         end rn
  from   totals t
), rws as (
  select r.sym, r.sm, 
         min(rn) over (partition by rm) mn
  from   rankings r
)
  select case when sym is null then 'Grand total' else sym end symname,
         "1", "2", "3", "4", "5", "6", "7"
  from   rws
  pivot  (
    max(sm) for mn in (1, 2, 3, 4, 5, 6, 7)
  )
  order  by sym nulls last;

SYMNAME                                                                                   1        2        3        4        5        6        7    
Blood & Circulation, Haemorrhage (bleeding), from inner parts                                    4        4        4        3        4        4    1 
Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)                                   4        4        4        4        3        3    2 
Modalities, situation & circumstances, Rising, on (on straightening-up, becoming erect)          4        4        3        4        4        3    2 
Modalities, situation & circumstances, Teeth, cleaning, from                                <null>   <null>   <null>   <null>   <null>   <null>    3 
Teeth & Gums, Gums                                                                               4        3        3        2        2        2    4 
Grand total                                                                                     16       15       14       13       13       12   12


Of course, the problem here is you don't know which remname each column applies to!

You could get the corresponding rename as a row value by adding it to the pivot like so:

with totals as (
  select "symname" sym, "remname" rm, sum("grade") sm, count("symname") ct
  from   remsym
  where  "remname" in ( 'Nux-v', 'Bell', 'Phos', 'Sulf', 'Rhus', 'Puls', 'Staph' )
  and    "symname" in (
    'Blood & Circulation, Haemorrhage (bleeding), from inner parts',
    'Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)',
    'Modalities, situation & circumstances, Rising, on (on straightening-up, becoming erect)',
    'Modalities, situation & circumstances, Teeth, cleaning, from',
    'Teeth & Gums, Gums'
  )
  group by grouping sets ( ("remname", "symname"), "remname") 
), rankings as (
  select t.*,
         case 
           when sym is null then row_number() over (partition by sym order by sm desc, ct) 
         end rn
  from   totals t
), rws as (
  select r.sym, r.sm, r.rm,
         min(rn) over (partition by rm) mn
  from   rankings r
)
  select case when sym is null then 'Grand total' else sym end symname,
         "1_TOT", "1_RM",
         "2_TOT", "2_RM",
         "3_TOT", "3_RM",
         "4_TOT", "4_RM"
  from   rws
  pivot  (
    max(sm) tot, min(rm) rm for mn in (1, 2, 3, 4, 5, 6, 7)
  )
  order  by sym nulls last;

SYMNAME                                                                                   1_TOT    1_RM     2_TOT    2_RM     3_TOT    3_RM     4_TOT    4_RM     
Blood & Circulation, Haemorrhage (bleeding), from inner parts                                    4 Nux-v           4 Bell            4 Phos            3 Rhus     
Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)                                   4 Nux-v           4 Bell            4 Phos            4 Rhus     
Modalities, situation & circumstances, Rising, on (on straightening-up, becoming erect)          4 Nux-v           4 Bell            3 Phos            4 Rhus     
Modalities, situation & circumstances, Teeth, cleaning, from                                <null> <null>     <null> <null>     <null> <null>     <null> <null>   
Teeth & Gums, Gums                                                                               4 Nux-v           3 Bell            3 Phos            2 Rhus     
Grand total                                                                                     16 Nux-v          15 Bell           14 Phos           13 Rhus


(columns limited for brevity)

But that will make your already large 126 column report even more unwieldy. To avoid this and have the remnames as column headings, I believe you need to go down the dynamic SQL route. Generate your desired column order by passing the sorting above to listagg:

with totals as (
  select "remname" rm, sum("grade") sm, count("symname") ct
  from   remsym
  where  "remname" in ( 'Nux-v', 'Bell', 'Phos', 'Sulf', 'Rhus', 'Puls', 'Staph' )
  and    "symname" in (
    'Blood & Circulation, Haemorrhage (bleeding), from inner parts',
    'Generals, Mind, Sensorium, Vertigo (dizziness, giddiness etc.)',
    'Modalities, situation & circumstances, Rising, on (on straightening-up, becoming erect)',
    'Modalities, situation & circumstances, Teeth, cleaning, from',
    'Teeth & Gums, Gums'
  )
  group by "remname"
)
  select listagg(rm, ',') within group (order by sm desc, ct)
  from   totals;

LISTAGG(RM,',')WITHINGROUP(ORDERBYSMDESC,CT)   
Nux-v,Bell,Phos,Rhus,Sulf,Puls,Staph


The paste this string into the IN clause of the pivot using dynamic SQL.

If this is doesn't work for you, you could look into using Anton Sheffer's dynamic pivot function:

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
https://technology.amis.nl/2006/05/16/pivot-dynamic-data/

PS - please please please don't use "quoted_lowercase_column_names"! This makes them case sensitive. So to access them you always have to use quotes. Which kinda sucks.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Multiple summaries and automatic sorting

Kaushal Ruparel, April 07, 2018 - 9:02 am UTC

Hello Chris,

I appreciate your time and effort behind providing the answer, but it lacks the multiple summaries and automatic horizontal sorting.

In the original question, apart from "Sum of grades (Grand Total)", count of non-null values (as a row beneath Grand Total) is a primary requirement and horizontal sorting by count + sum will decide the final pivot.

Filter on symname will change the count and sum of grades for various remname and by sorting we ensure that the top remname satisfying the filter appears first.

There is no restriction in changing the table design at this point and if the problem has its solution in redesigning the table then it is 100% doable.

I have tried various methods but was unable to obtain the desired output. Also I tried anton's dynamic pivot, but unfortunately, I was not able to run it on livesql (got error in odcidescribecall).

Getting two summary lines

Stew Ashton, April 07, 2018 - 2:58 pm UTC

Hello,

I think I found a way to get both a 'Total' line and a 'Count' line. I have changed the column names to upper case.
select SYMNAME,
  "Nux-v","Bell","Phos","Rhus","Sulf","Puls","Staph"
from (
  select
    grouping_id(symname, 0) symname_order,
    case grouping_id(symname, 0)
      when 2 then 'Grand Total'
      when 3 then 'Count'
      else symname
    end symname,
    remname,
    case grouping_id(symname, 0)
      when 3 then count(grade)
      else sum(grade)
    end grade
  from remsym
  where symnum in (659, 815, 2120, 2200, 220)
  group by grouping sets( (remname), (remname,0), (symname, remname) )
)
pivot(max(grade) for remname in (
  'Nux-v' as "Nux-v",'Bell' as "Bell",'Phos' as "Phos",'Rhus' as "Rhus",'Sulf' as "Sulf",'Puls' as "Puls",'Staph' as "Staph"
))
order by symname_order, symname;

SYMNAME                                   Nux-v   Bell   Phos   Rhus   Sulf   Puls   Staph 
Blood & Circulation, Haemorrhage (bleedi      4      4      4      3      4      4       1 
Generals, Mind, Sensorium, Vertigo (dizz      4      4      4      4      3      3       2 
Modalities, situation & circumstances, R      4      4      3      4      4      3       2 
Modalities, situation & circumstances, T                                                 3 
Teeth & Gums, Gums                            4      3      3      2      2      2       4 
Grand Total                                  16     15     14     13     13     12      12 
Count                                         4      4      4      4      4      4       5

To get the column names ordered properly, I use an intermediate SQL statement to generate the final SQL statement. This is the first method mentioned by Chris.
with template as ( select
q'<select SYMNAME,
  #COL_LIST#
from (
  select
    grouping_id(symname, 0) symname_order,
    case grouping_id(symname, 0)
      when 2 then 'Grand Total'
      when 3 then 'Count'
      else symname
    end symname,
    remname,
    case grouping_id(symname, 0)
      when 3 then count(grade)
      else sum(grade)
    end grade
  from remsym
  where symnum in (659, 815, 2120, 2200, 220)
  group by grouping sets( (remname), (remname,0), (symname, remname) )
)
pivot(max(grade) for remname in (
  #PIVOT_LIST#
))
order by symname_order, symname>'
txt from dual
  )
, parms as (
  select
    listagg('"'||remname||'"', ',')
      within group(order by sum(grade) desc, count(grade))
    col_list,
    listagg(''''||remname||''' as "'||remname||'"', ',')
      within group(order by sum(grade) desc, count(grade))
    pivot_list
  from remsym 
  where symnum in (659, 815, 2120, 2200, 220)
  group by remname
)
select replace(
  replace(
    txt, 
    '#COL_LIST#', 
    col_list
  ), 
  '#PIVOT_LIST#', 
  pivot_list
)
from template, parms;

Result:

select SYMNAME,
  "Nux-v","Bell","Phos","Rhus","Sulf","Puls","Staph","Calc","Bry","Merc","Nat-m","Sepia","Lyc","Acon","Arn","Nit-ac","Ferr","Ars","Carb-v","Cham","Con","Sil","Carb-a","Caust","Chin","Hep","Ph-ac","Zinc","Borx","Cann-s","Canth","Cic","Op","Sabin","Stram","Bov","Caps","Kali-c","Mur-ac","Sul-ac","Ip","Cocc","Croc","Dros","Graph","Hyos","Ign","Petr","Scill","Sec-c","Tarx","Thuj","Verat","Amm-m","Anac","Ruta","Sars","Cupr","Laur","Ran-b","Ambr","Amm-c","Ant-c","Asar","Iod","Nat-c","Sabad","Spong","Alum","Arg","Bar-c","Mag-m","Plb","Stann","Led","Mosch","Valer","Agar","Colch","Coloc","Creos","Dig","Kali-n","M-arc","Nux-m","Rhod","Viol-t","Aur","Calad","Dulc","Mez","Spig","Stront","Bism","Lach","M-amb","M-aus","Mag-c","Par","Plat","Eupho","Mang","Olnd","Rheum","Verba","Viol-o","Ang","Ant-t","Chel","Coff","Euphr","Hell","Meny","Ran-s","Seneg","Vitx","Asaf","Camph","Cina","Clem","Cycl","Mar","Samb","Selen"
from (
  select
    grouping_id(symname, 0) symname_order,
    case grouping_id(symname, 0)
      when 2 then 'Grand Total'
      when 3 then 'Count'
      else symname
    end symname,
    remname,
    case grouping_id(symname, 0)
      when 3 then count(grade)
      else sum(grade)
    end grade
  from remsym
  where symnum in (659, 815, 2120, 2200, 220)
  group by grouping sets( (remname), (remname,0), (symname, remname) )
)
pivot(max(grade) for remname in (
  'Nux-v' as "Nux-v",'Bell' as "Bell",'Phos' as "Phos",'Rhus' as "Rhus",'Sulf' as "Sulf",'Puls' as "Puls",'Staph' as "Staph",'Calc' as "Calc",'Bry' as "Bry",'Merc' as "Merc",'Nat-m' as "Nat-m",'Sepia' as "Sepia",'Lyc' as "Lyc",'Acon' as "Acon",'Arn' as "Arn",'Nit-ac' as "Nit-ac",'Ferr' as "Ferr",'Ars' as "Ars",'Carb-v' as "Carb-v",'Cham' as "Cham",'Con' as "Con",'Sil' as "Sil",'Carb-a' as "Carb-a",'Caust' as "Caust",'Chin' as "Chin",'Hep' as "Hep",'Ph-ac' as "Ph-ac",'Zinc' as "Zinc",'Borx' as "Borx",'Cann-s' as "Cann-s",'Canth' as "Canth",'Cic' as "Cic",'Op' as "Op",'Sabin' as "Sabin",'Stram' as "Stram",'Bov' as "Bov",'Caps' as "Caps",'Kali-c' as "Kali-c",'Mur-ac' as "Mur-ac",'Sul-ac' as "Sul-ac",'Ip' as "Ip",'Cocc' as "Cocc",'Croc' as "Croc",'Dros' as "Dros",'Graph' as "Graph",'Hyos' as "Hyos",'Ign' as "Ign",'Petr' as "Petr",'Scill' as "Scill",'Sec-c' as "Sec-c",'Tarx' as "Tarx",'Thuj' as "Thuj",'Verat' as "Verat",'Amm-m' as "Amm-m",'Anac' as "Anac",'Ruta' as "Ruta",'Sars' as "Sars",'Cupr' as "Cupr",'Laur' as "Laur",'Ran-b' as "Ran-b",'Ambr' as "Ambr",'Amm-c' as "Amm-c",'Ant-c' as "Ant-c",'Asar' as "Asar",'Iod' as "Iod",'Nat-c' as "Nat-c",'Sabad' as "Sabad",'Spong' as "Spong",'Alum' as "Alum",'Arg' as "Arg",'Bar-c' as "Bar-c",'Mag-m' as "Mag-m",'Plb' as "Plb",'Stann' as "Stann",'Led' as "Led",'Mosch' as "Mosch",'Valer' as "Valer",'Agar' as "Agar",'Colch' as "Colch",'Coloc' as "Coloc",'Creos' as "Creos",'Dig' as "Dig",'Kali-n' as "Kali-n",'M-arc' as "M-arc",'Nux-m' as "Nux-m",'Rhod' as "Rhod",'Viol-t' as "Viol-t",'Aur' as "Aur",'Calad' as "Calad",'Dulc' as "Dulc",'Mez' as "Mez",'Spig' as "Spig",'Stront' as "Stront",'Bism' as "Bism",'Lach' as "Lach",'M-amb' as "M-amb",'M-aus' as "M-aus",'Mag-c' as "Mag-c",'Par' as "Par",'Plat' as "Plat",'Eupho' as "Eupho",'Mang' as "Mang",'Olnd' as "Olnd",'Rheum' as "Rheum",'Verba' as "Verba",'Viol-o' as "Viol-o",'Ang' as "Ang",'Ant-t' as "Ant-t",'Chel' as "Chel",'Coff' as "Coff",'Euphr' as "Euphr",'Hell' as "Hell",'Meny' as 
"Meny",'Ran-s' as "Ran-s",'Seneg' as "Seneg",'Vitx' as "Vitx",'Asaf' as "Asaf",'Camph' as "Camph",'Cina' as "Cina",'Clem' as "Clem",'Cycl' as "Cycl",'Mar' as "Mar",'Samb' as "Samb",'Selen' as "Selen"
))
order by symname_order, symname;

Please note that I have hard-coded the SYMNUM list. Obviously, you need to change this, but how depends on where you are calling the SQL from.

Best regards, Stew Ashton
Chris Saxon
April 09, 2018 - 9:59 am UTC

Great work Stew, thanks for helping out.

Task accomplished!!!

Kaushal Ruparel, April 07, 2018 - 4:33 pm UTC

Amazing solution Mr. Stew Ashton!!!

Just tweaked the order by in below to suit the requirement and bingo.

within group(order by count(grade) desc, sum(grade) desc)
    col_list,
    listagg(''''||remname||''' as "'||remname||'"', ',')
      within group(order by count(grade) desc, sum(grade) desc)


I truly believe SQL is a flourished language!

Thanks a Ton.

one workaoourd pls

A reader, July 24, 2018 - 4:26 pm UTC

very good solution from you and stew

but how make it for more than 32000 char?

for instance, how replace the

listagg('"'||remname||'"', ',')
within group(order by sum(grade) desc, count(grade))
col_list,

by
rtrim( xmlagg( xmlelement( e,'"'||remname||'"'||',' ) ).extract( '//text()').getClobVal(), ',' )

cause the "&quot" is substituting thne '"'.
and utl_i18n.unescape_reference dosent work on clob !!

any tip?
Chris Saxon
July 25, 2018 - 10:41 am UTC

I'm not sure what you're trying to do here. What's with the XML extract?

Same stew qry but for cols exceeding listagg limit

A reader, July 25, 2018 - 11:17 am UTC

Chris.
I tried above Stew qry . But including lot of columns in listagg.
I tried your solution in some post using the xmlagg to bypass listagg limit but this not works


So please could rewrite the same qry above in order to make it work with s clob ?
Chris Saxon
July 25, 2018 - 1:22 pm UTC

I'm still not sure what you're trying to do. Show us all of your code!

Same test case

A reader, July 26, 2018 - 5:58 am UTC

with template as ( select q'<select SYMNAME, #COL_LIST# from ( select grouping_id(symname, 0) symname_order, case grouping_id(symname, 0) when 2 then 'Grand Total' when 3 then 'Count' else symname end symname, remname, case grouping_id(symname, 0) when 3 then count(grade) else sum(grade) end grade from remsym where symnum in (select summum from remsym ) group by grouping sets( (remname), (remname,0), (symname, remname) ) ) pivot(max(grade) for remname in ( #PIVOT_LIST# )) order by symname_order, symname>' txt from dual ) , parms as ( select listagg('"'||remname||'"', ',') within group(order by sum(grade) desc, count(grade)) col_list, listagg(''''||remname||''' as "'||remname||'"', ',') within group(order by sum(grade) desc, count(grade)) pivot_list from remsym where symnum in (select summum from remsym) group by remname ) select replace( replace( txt, '#COL_LIST#', col_list ), '#PIVOT_LIST#', pivot_list ) from template, parms;



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.