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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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




    dynsql1            varchar;

    dynsql2            varchar;

    columnlist       varchar;


-- 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


                                                                                                ' 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;




  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.




+1 240-483-1016



[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux