How to dynamically pivot based on rows data and parameter value?

Question

I am trying to pivot using crosstab function and unable to achieve for the requirement. Is there is a way to perform crosstab dynamically and also dynamic result set?

I have tried using crosstab built-in function and unable to meet my requirement.

select * from crosstab ('select  item,cd, type, parts, part, cnt
  from item 
  order by 1,2')
  AS results (item text,cd text, SUM NUMERIC, AVG NUMERIC);

Sample Data:

ITEM    CD  TYPE    PARTS   PART    CNT
Item 1  A   AVG 4   1   10
Item 1  B   AVG 4   2   20
Item 1  C   AVG 4   3   30
Item 1  D   AVG 4   4   40
Item 1  A   SUM 4   1   10
Item 1  B   SUM 4   2   20
Item 1  C   SUM 4   3   30
Item 1  D   SUM 4   4   40

Expected Results:

ITEM    CD  PARTS   TYPE_1  CNT_1   TYPE_1  CNT_1   TYPE_2  CNT_2   TYPE_2  CNT_2   TYPE_3  CNT_3   TYPE_3  CNT_3   TYPE_4  CNT_4   TYPE_4  CNT_4
Item 1  A   4   AVG 10  SUM 10  AVG 20  SUM 20  AVG 30  SUM 30  AVG 40  SUM 40

The PARTS value is based on a parameter passed by the user. If the user passes 2 for example, there will be 4 rows in the result set (2 parts for AVG and 2 parts of SUM).

Can I achieve this requirement using CROSSTAB function or is there a custom SQL statement that need to be developed?

In general pivot/crosstab is better done in the application. SQL isn't really suited for that. If you really need to get everything in a single row, consider aggregating the "columns" into a JSON value. — , Aug 10 '19 at 08:30

Morris de Oryx · Answer 1 · 2019-08-10T08:24:37.377

I'm not following your data, so I can't offer examples based on it. But I have been looking at pivot/cross-tab features over the past few days. I was just looking at dynamic cross tabs just before seeing your post. I'm hoping that your question gets some good answers, I'll start off with a bit of background.

You can use the crosstab extension for standard cross tabs, what when wrong when you tried it? Here's an example I wrote for myself the other day with a bunch of comments and aliases for clarity. The pivot is looking at item scans to see where the scans were "to", like the warehouse or the floor.

/* Basic cross-tab example for crosstab (text) format of pivot command.
   Notice that the embedded query has to return three columns, see the aliases.
   #1 is the row label, it shows up in the output.
   #2 is the category, what determines how many columns there are. *You have to work this out in advance to declare them in the return.*
   #3 is the cell data, what goes in the cross tabs. Note that this form of the crosstab command may return NULL, and coalesce does not work.
   To get rid of the null count/sums/whatever, you need crosstab (text, text).
*/  

    select *
      from crosstab ('select 
                      specialty_name            as row_label,
                      scanned_to                as column_splitter,
                      count(num_inst)::numeric  as cell_data
                      from scan_table
                      group by 1,2
                      order by 1,2')

                  as scan_pivot (
                      row_label citext,
                      "Assembly" numeric,
                      "Warehouse" numeric,
                       "Floor" numeric,
                       "QA" numeric);

As a manual alternative, you can use a series of FILTER statements. Here's an example that summaries errors_log records by day of the week. The "down" is the error name, the "across" (columns) are the days of the week.

  select "error_name",
         count(*) as "Overall",
         count(*) filter (where extract(dow from "updated_dts") = 0) as "Sun",
         count(*) filter (where extract(dow from "updated_dts") = 1) as "Mon",
         count(*) filter (where extract(dow from "updated_dts") = 2) as "Tue",
         count(*) filter (where extract(dow from "updated_dts") = 3) as "Wed",
         count(*) filter (where extract(dow from "updated_dts") = 4) as "Thu",
         count(*) filter (where extract(dow from "updated_dts") = 5) as "Fri",
         count(*) filter (where extract(dow from "updated_dts") = 6) as "Sat"

    from error_log

   where "error_name" is not null

group by "error_name"
order by 1;

You can do the same thing with CASE, but FILTER is easier to write.

It looks like you want something basic, maybe the FILTER solution appeals? It's easier to read than calls to crosstab(), since that was giving you trouble.

FILTER may be slower than crosstab. Probably. (The crosstab extension is written in C, and I'm not sure how smart FILTER is about reading off indexes.) But I'm not sure as I haven't tested it out yet. (It's on my to do list, but I haven't had time yet.) I'd be super interested if anyone can offer results. We're on 11.4.

I wrote a client-side tool to build FILTER-based pivots over the past few days. You have to supply the down and across fields, an aggregate formula and the tool spits out the SQL. With support for coalesce for folks who don't want NULL, ROLLUP, TABLESAMPLE, view creation, and some other stuff. It was a fun project. Why go to that effort? (Apart from the fun part.) Because I haven't found a way to do dynamic pivots that I actually understand. I love this quote:

"Dynamic crosstab queries in Postgres has been asked many times on SO all involving advanced level functions/types. Consider building your needed query in application layer (Java, Python, PHP, etc.) and pass it in a Postgres connected query call. Recall SQL is a special-purpose, declarative type while app layers are general-purpose, imperative types." – Parfait

So, I wrote a tool to pre-calculate and declare the output columns. But I'm still curious about dynamic options in SQL. If that's of interest to you, have a look at these two items:

https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html

Flatten aggregated key/value pairs from a JSONB field?

Deep magic in both.

How to dynamically pivot based on rows data and parameter value?

1 Answers1