Thread: Dynamically generate varying Number of Column Headers from the Row data
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
)
;
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;
select * from xtx('xtabtest','d2','d3','1'); select * from xtab;
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
Hi All,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