Prefixing few more columns to CROSSTAB result set- Row-Data

[M Sarwar <sarwarmd02@xxxxxxxxxxx>]

 

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