From: Adrian Klaver <adrian.klaver@aklaver.com>
Subject: Re: Crosstab function
To: Gowtham Vel <c8gowthamvel@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>
Cc: pgsql-sql@postgresql.org
Message-ID: <270d8a50-574e-74b7-d487-50ad17725df9@aklaver.com>
Date: Wed, 5 Apr 2017 17:01:47 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAH-J9gYZrGyfMGwACi61-r6rqiMcme3cf3zTseHDpK4UnM36ug@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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