Thread: Prefixing few more columns to CROSSTAB result set- Row-Data
Hi All,
In my CROSSTAB SQL, column data is dynamic. That means, number of heading columns will vary from time to time.
I am listing the function code.
-- pivotcode beginning --------x
CREATE OR REPLACE FUNCTION bronxdev.Bronx_Mcm_Stage_Shipping_Pivot
RETURNS character varying AS
$BODY$
declare
dynsql1 varchar;
dynsql2 varchar;
columnlist varchar;
begin
-- Set 1. retrieve list of column names.
-- Below Line [and TESTC.TEST_NUMBER ] will go away after the trials
dynsql1 = 'SELECT STRING_AGG ( TESTC.TEST_NUMBER, '' text, '' ORDER BY TESTC.TEST_NUMBER ) || '' 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.STAGE = '|| '''FT''' ||
' and PART.RUN_ID = '|| '''607702''' ||
' GROUP by PART.SERIAL_NUMBER limit 1 '
;
-- RAISE info 'Hello!';
RAISE NOTICE 'dynsql1 - > %', dynsql1;
execute dynsql1 into columnlist;
-- RAISE NOTICE 'Test Numbers - > %', columnlist;
-- Set 2. set up the crosstab query
dynsql2 = '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.STAGE = '|| '''''FT'''''||
' and PART.RUN_ID = '|| '''''607702''''' ||
' ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER '' )' ||
' as ConcatenatedResults ( SERIAL_NUMBER character varying(18), '||columnlist||' );';
RAISE NOTICE 'dynsql2 - > %', dynsql2;
return dynsql2;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- pivotcode ending --------x
After generating the SQL needed for CROSSTAB in dynsql2, I need to add few columns from the same table to the SQL. CROSSTAB directly does not any option to have more than 1 column as ROW-DATA in the CROSSTAB clause.
I need to further develop the function to add few more columns from the table BRONXdev.TEST_PART_DETAILS_ALL_MCM.
In the above function, I have the SQL generated by dynsql2. I need to add columns to the SQL generated by dynsql2
I am unable to think on how to add few columns to the result set of CROSSTAB.
I would appreciate any help on this. Management is thinking that I do not know how to write a SQL which is absurd.
Please let me know if you need any additional information.
Thanks,
Sarwar
+1 240-483-1016