Re: Crosstab function

On 04/05/2017 10:04 AM, Gowtham Vel wrote:
> Hi Adrian,
>
> Could you please check and update on my below email

This would have happened sooner with a self contained test case e.g. a 
CREATE TABLE statement and COPY/INSERT statements for the data.

At any rate:

test=# \d crosstab_test      Table "public.crosstab_test" Column |       Type        | Modifiers
--------+-------------------+----------- id     | integer           | wf_id  | character varying | name   | character
varying| value  | character varying |
 

I lower cased the name values in the above.

Showing a small subset of the 80 some names you have:

SELECT    *
FROM    crosstab ('select wf_id, name, value from crosstab_test where name 
in(''interchangecontrolnumber'', ''ponumber'', ''docid'',''direction'', 
''docdate'') order by 1',
'select distinct name from crosstab_test where name 
in(''interchangecontrolnumber'', ''ponumber'', ''docid'', ''direction'', 
''docdate'') order by 1')
AS(wf_id INT,        direction VARCHAR,        docdate VARCHAR,        docid VARCHAR,        interchange VARCHAR,
po VARCHAR);
 


-[ RECORD 1 ]----------------------
wf_id       | 1627075
direction   | Inbound
docdate     | 20170316
docid       | 411069802
interchange | 2947
po          | 411069802
-[ RECORD 2 ]----------------------
wf_id       | 1652040
direction   | Outbound
docdate     | 20170319
docid       | 201703191489929516706
interchange | 7167
po          | NULL


For more information see:

https://www.postgresql.org/docs/9.5/static/tablefunc.html

F.36.1.4. crosstab(text, text)

>
> Thanks
> Gowtham K
>
>
> On Apr 4, 2017 11:58 PM, "Gowtham Vel" <c8gowthamvel@gmail.com
> <mailto:c8gowthamvel@gmail.com>> wrote:
>
>     Hi Adrian,
>
>     I have attached the input table and output table in below .xlsx sheet
>
>     1) I have removed the duplicate rows and sort the INPUT_TABLE  i.e.,
>     SELECT * FROM "public".INPUT_TABLE ORDER by 2,3
>     2) Input_table column name should be the column heading for
>     Output_table.
>     3) Input_table column value should be the rows for Output_table.
>     4) some values i have mention in null , because its for future
>     record(its might come)
>     5) please refer the below attached sheet and provide your assistance,
>
>     Regards,
>     Gowtham K
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com