agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Gowtham Vel <c8gowthamvel@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Crosstab function
Date: Wed, 5 Apr 2017 17:01:47 -0700
Message-ID: <270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com> (raw)
In-Reply-To: <CAH-J9gYZrGyfMGwACi61-r6rqiMcme3cf3zTseHDpK4UnM36ug@mail.gmail.com>
References: <CAH-J9gYJFdnmLrnAaFP=fm-Au5DHm-iT5=opSYJ3bX2mz7nSMg@mail.gmail.com>
<f5ff6486-be36-068f-1d36-5584cc1cb27d@aklaver.com>
<CAH-J9gYKOH+WFHYYVw7YsrkU3YZ8GMFYaUnjEe-FwXxvkFFEDg@mail.gmail.com>
<CAH-J9gYZrGyfMGwACi61-r6rqiMcme3cf3zTseHDpK4UnM36ug@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (10+ messages)
Message-ID: <270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com>
Permalink: ../270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com/
Also on: postgresql.org/message-id/270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, c8gowthamvel@gmail.com
Subject: Re: Crosstab function
In-Reply-To: <270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox