I have one table in Postgres which has following columns:

processtransactionid bigserial,
TenantID uuid,
ProcessInstanceId bigint
attributename varchar(250),
attributevalue varchar(250),  
createddate timestamp,
queueid int

The attributename contains values like 'filename', 'documentstatus', 'batchImportdate', etc. and attributevalue columns contains corresponding values of the attribute, like filename = abc.jpg, documentstatus ='Present' & batchImportdate='2019/08/26 12:57:51'

I want output in the following way:

 ProcessInstanceId filename  documentstatus batchImportdate        queueid 
   6008            abc.jpg   Present        '2019/08/26 12:57:51'  2
   6008            abc.jpg   Absent         '2019/08/26 12:57:51'  3

grouping of the records to be done by ProcessInstanceId, queueid

I am able to create query in SQL Server using the PIVOT keyword but facing a problem in creating the same query in Postgres.

Could you please help me write the Postgres query?

  • 1
    PostgreSQL does not have the PIVOT keyword. Instead, you can use the crosstab() extension as detailed here. p.s. welcome to the forum! :-) p.p.s. In future, could you provide a fiddle with your sample data and your desired result as detailed in articles in my profile? For example, you could have given your working SQL Server query as the example of what you wanted to do?
    – Vérace
    Aug 26, 2019 at 7:54
  • You've been told that PostgreSQL doesn't have the PIVOT keyword. You've been given (a very detailed) outline of how to do this in PostgreSQL - Erwin Brandstetter is one of the top PG guys here. Now, if you can't get the query to run as you with, I suggest that you delete this question and repost it as (something like) Have query working in MS SQL, how to get it to work in PostgreSQL? and include the working MS query in a fiddle!
    – Vérace
    Aug 26, 2019 at 15:49


Browse other questions tagged or ask your own question.