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(at)gmail(dot)com
> <mailto:c8gowthamvel(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com