Thread: Crosstab function
Hi postgresql support, Could you please help on crosstab function for dynamic column. Regards, Gowtham K
On 04/02/2017 09:05 AM, Gowtham Vel wrote: > Hi postgresql support, > > Could you please help on crosstab function for dynamic column. The docs and an example: https://www.postgresql.org/docs/9.6/static/tablefunc.html http://stackoverflow.com/questions/3002499/postgresql-crosstab-query If you need something more specific, then we will need to see an example of what you are trying to achieve. > > Regards, > Gowtham K > > -- Adrian Klaver adrian.klaver@aklaver.com
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
Attachment
Hi Adrian,
Could you please check and update on my below email
Thanks
Gowtham K
On Apr 4, 2017 11:58 PM, "Gowtham Vel" <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
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