Re: Dynamically generate varying Number of Column Headers from the Row data

hector vass <hector.vass@xxxxxxxxx> · Wed, 13 Mar 2024 22:06:08 +0000

I think you are referring to 
create extension tablefunc;
select * from crosstab('
    select
      dimensionX,
      dimensionY,
      fact
   from source_table
') as ct(Xdimension text, YCategory1_value text, YCategory2_value, YCategory3_value);

And the limitation that this extension has, in that, you need to know and name each value that dimensionY can take in advance.  

I think you are wanting to dynamically create the cross tab from an unknown number of distinct dimensionY values.  To do this you need to write a function that creates then executes the query.  The rub comes from the fact that you still have to define the return record for the function.  There are a couple of ways around this, create a table / view then select * from it or return a single column with a flexible structure within it such as JSON.

The basic form of a cross tab is sum(if or in sql sum(case when

select
    dimensionX,
    sum(case when dimensionY='A' then fact else 0 end) as dimensionY_A,
    sum(case when dimensionY='B' then fact else 0 end) as dimensionY_B,
    sum(case when dimensionY='C' then fact else 0 end) as dimensionY_C,
    .....
from source_table
group by dimensionX;

You need plpgsql or similar to create the sum(if rows in a function...
something like this

create table xtabtest as 
select 
	*,
	floor(random()*10) as v1,
	floor(random()*90)+10 as v2
from (values('A'),('B'),('C')) as d1(d1),
(values('alpha'),('beta'),('gamma')) as d2(d2),
(values('red'),('orange'),('yellow')) as d3(d3);

create or replace function xtx(tablename text,x text,y text,v text) 
returns void
language plpgsql 
as $function$
declare 
	cmd text;
	sqlcmd text;
begin
	cmd='drop view if exists xtab';
	execute cmd;
	cmd=$$with cte_cols(dims) as ( 
	    select $$||y||$$ from $$||tablename||$$ group by 1
	), cte_cmd as (
	    select 'create temporary view xtab as (select $$||x||$$' as sqlcmd
	    union all 
	    select ',sum(case when $$||y||$$='''||dims||''' then $$||v||$$ else 0 end) as $$||y||$$_'||dims from cte_cols
	    union all 
	    select 'from $$||tablename||$$ group by 1 order by 1)'
	) select string_agg(sqlcmd,' ') from cte_cmd
	$$;
	execute cmd into sqlcmd;
	execute sqlcmd;
end;
$function$;

select * from xtx('xtabtest','d1','d2','v1'); select * from xtab;
select * from xtx('xtabtest','d1','d3','v1'); select * from xtab;
select * from xtx('xtabtest','d2','d3','v2'); select * from xtab;

You can generalize this a little
If you wanted to count rather than sum
select * from xtx('xtabtest','d2','d3','1'); select * from xtab;
If you wanted to have multiple x dimension 
create view compview as (select d1||'.'||d2 as compX,* from xtabtest);
select * from xtx('compview','compx','d3','v1'); select * from xtab;

The caveat for all this 
...it is end users who want things to go across the page, analysts know you lose value of data when you do this
...so at which point it is not really data or analysis it is presentation
...would it not be better to produce the cross tab in the front end application layer rather than the data layer

On Mon, Mar 11, 2024 at 4:58 PM M Sarwar <sarwarmd02@xxxxxxxxxxx> wrote:

Hi All,

https://www.postgresql.org/docs/current/tablefunc.html#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT

I have followed the above link as per which we need to mention all the column headers /category and I am unable to achieve dynamically changing column headers. 
 Column header will vary  from time to time.

SELECT * FROM CROSSTAB           (
                                                'SELECT                                                               
 PART.SERIAL_NUMBER ,  TESTC.TEST_NUMBER,

                                                                TRESULT.TEST_RESULT::text
FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
                BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
                BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
WHERE  PART.TEST_PART_DET_ALL_MCM_ID                                                       = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
                                AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
                                AND PART.DATE1                                                                                                                                             
 = ''12/01/2023''
                                and PART.STAGE                                                                                                                                = ''FT''
                                AND TESTC.TEST_NUMBER                                                                                                           in ( ''TEST1P1'',
 ''TEST1P2'', ''TEST1P3'', ''TEST1P4'' , ''TEST1P5'' ,
                                                                                                                                                                                ''TEST1P6'',
 ''TEST1P7'' , ''TEST1P8'', ''TEST1P9'',
                                                                                ''TEST2P1'', ''TEST2P10'', ''TEST2P12'', ''TEST2P13'', ''TEST2P14'', ''TEST2P15'', ''TEST2P16'', ''TEST2P17'',
 ''TEST2P18'', ''TEST2P19'',
                                ''TEST2P2'',

                                                                                ''TEST2P20'', ''TEST2P21'', ''TEST2P22'', ''TEST2P23'', ''TEST2P24'', ''TEST2P25'', ''TEST2P26'', ''TEST2P27'', ''TEST2P28'', ''TEST2P29'',
                ''TEST2P3'', ''TEST2P30'', ''TEST2P32'', ''TEST2P33'', ''TEST2P35'', ''TEST2P36'', ''TEST2P37'', ''TEST2P38'', ''TEST2P39'',
                ''TEST2P4'',
                ''TEST2P40'', ''TEST2P41'', ''TEST2P42'', ''TEST2P43'', ''TEST2P44'', ''TEST2P45'', ''TEST2P46'', ''TEST2P47'', ''TEST2P48'', ''TEST2P49'',
                ''TEST2P5'', ''TEST2P50'', ''TEST2P51'', ''TEST2P52'', ''TEST2P53'', ''TEST2P54'', ''TEST2P55'', ''TEST2P56'', ''TEST2P57'', ''TEST2P58'', ''TEST2P59'',
                ''TEST2P6'', ''TEST2P7'', ''TEST2P8'', ''TEST2P9''

                 )
ORDER BY 1, 2'

                                )
                                AS Concatenated ( SERIAL_NUMBER character varying(18), TEST1P1 text, TEST1P2 text, TEST1P3 text                , TEST1P4 text, TEST1P5 text,
                                                                                                  TEST1P6 text, TEST1P7 text , TEST1P8 text, TEST1P9  text,
                TEST2P1 text, TEST2P10 text, TEST2P12 text, TEST2P13 text, TEST2P14 text, TEST2P15 text, TEST2P16 text, TEST2P17 text, TEST2P18 text, TEST2P19 text,
                TEST2P2 text,
                TEST2P20 text, TEST2P21 text, TEST2P22 text, TEST2P23 text, TEST2P24 text, TEST2P25 text, TEST2P26 text, TEST2P27 text, TEST2P28 text, TEST2P29 text,
                                                                                                TEST2P3 text,
                TEST2P30 text, TEST2P32 text, TEST2P33 text, TEST2P35 text, TEST2P36 text, TEST2P37 text, TEST2P38 text, TEST2P39 text,

                                                                                                TEST2P4 text,
                TEST2P40 text, TEST2P41 text, TEST2P42 text, TEST2P43 text, TEST2P44 text, TEST2P45 text, TEST2P46 text, TEST2P47 text,TEST2P48 text, TEST2P49 text,
                                                                                                TEST2P5 text, TEST2P6 text, TEST2P7 text, tTEST2P8 text, TEST2P9 text
                                                                                                )
;

In the above SQL, I mentioned Test_number specifically.

I need to have the ability to change number Test_Numbers from the above SQL dynamically and without mentioning any Test_Numbers. Is that possible?

 I appreciate your help in this regard.

Thanks,

Sarwar