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

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(at)outlook(dot)com> 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
>
>