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