From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Gowtham Vel <c8gowthamvel(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Crosstab function |
Date: | 2017-04-06 00:01:47 |
Message-ID: | 270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2017-04-07 02:32:31 | death of array? |
Previous Message | Gowtham Vel | 2017-04-05 17:04:06 | Re: Crosstab function |