Pivot in PostgreSQL

Question

I am trying to find a way to do pivot in Postgres but I can’t use it and I am trying to find another way. I found the following website that explains pivot in SQL Server and in example one is what exactly I want to do. http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/

The example is:

CREATE TABLE CourseSales(Course VARCHAR(50),Year INT,Earning  MONEY);

INSERT INTO CourseSales VALUES('.NET',2012,10000);
INSERT INTO CourseSales VALUES('Java',2012,20000);
INSERT INTO CourseSales VALUES('.NET',2012,5000);
INSERT INTO CourseSales VALUES('.NET',2013,48000);
INSERT INTO CourseSales VALUES('Java',2013,30000);

With the pivot function the query is the following:

SELECT *FROM CourseSales
PIVOT(SUM(Earning)       
FOR Course IN ([.NET], Java)) AS PVTTable;

I would like to do the same in postgresql but with a dynamic way like the above.

This answer might be a duplicate and contain the information you want. I won't flag it as duplicate as I'm not sure though. http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 — jpw, Jul 07 '15 at 18:07

George Christou · Accepted Answer · 2015-07-10T16:19:03.623

Unfortunate I, couldn't find a way to pivot my table using postgresql and have a result in the form that I need, but I try to do it on R, where I found it much easier.

I connect my postgres database with R and then I use the reshape package to perform a pivot in my table.

Just for any of you that you would like to use my method, here is what I did.

First connect R with the database.

library(RPostgreSQL)
#create driver
dDriver <- dbDriver("PostgreSQL")
#connect to the server
conn <- dbConnect(dDriver, user="user", password="mypassword", dbname="postgres")

Then use the reshape package

library(reshape)

Fetch the table

rs_CourseSales <- dbSendQuery(conn, 'SELECT * FROM CourseSales;')
dbRows_CourseSales <- fetch(rs_CourseSales, -1)

Pivot:

pivot_CourseSales<-(cast(dbRows_CourseSales,  Course ~ Year))

I found this example very useful: http://www.r-bloggers.com/pivot-tables-in-r/

Xedni · Answer 2 · 2015-07-07T18:01:54.737

I'm not all that familiar with postgresql myself, but there is an alternate way to pivot date known colloquially as cross-tab-pivoting. Here's an example of how you could rewrite your query above, and I'm pretty sure all these operators would exists in postgre

CREATE TABLE CourseSales(Course VARCHAR(50),Year INT,Earning  MONEY);

INSERT INTO CourseSales VALUES('.NET',2012,10000);
INSERT INTO CourseSales VALUES('Java',2012,20000);
INSERT INTO CourseSales VALUES('.NET',2012,5000);
INSERT INTO CourseSales VALUES('.NET',2013,48000);
INSERT INTO CourseSales VALUES('Java',2013,30000);

-- Your version (AKA TSQL)
SELECT *FROM CourseSales
PIVOT(SUM(Earning)       
FOR Course IN ([.NET], Java)) AS PVTTable;

-- Cross-tab
select 
    Year,
    sum(case when Course = '.NET' then Earning else 0 end) as net,
    sum(case when Course = 'Java' then Earning else 0 end) as java
from CourseSales
group by year

Here's an article on cross tab pivots. Again, I know this is TSQL specific, but the operators should allow for similar functionality in any ansi sql dialect.

http://www.sqlservercentral.com/articles/T-SQL/63681/

So, as I mentioned, postgresql is not my forte. However the reasons you're giving for why it won't work are just syntactical things. If you can't use square brackets, use double quotes. If you cant alias a column with an equals sign, alias it using `AS` — Xedni, Jul 07 '15 at 18:00

Pivot in PostgreSQL

2 Answers2