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
