How do We Transform sql server 2012 query syntax into postgresql or standard sql

Question

We tried to transposing of data using unpivot operator in sql server 2012.same thing we have to output using postgres database. so,we have to rewrite given syntax into postgresql.

We have also tried on postgresql : uncrosstab() function using given query structure.

select * from uncrosstab( select * from tablename) as ct()

ie Our input sql server 2012 syntax is :

select Name,budget,CASE WHEN bmon='BudMnt1' THEN CONVERT(DATE, '01-JAN-2015')
WHEN bmon='BudMnt2' THEN CONVERT(DATE, '01-FEB-2015')
WHEN bmon='BudMnt3' THEN CONVERT(DATE, '01-MAR-2015')
WHEN bmon='BudMnt4' THEN CONVERT(DATE, '01-APR-2015')
WHEN bmon='BudMnt5' THEN CONVERT(DATE, '01-MAY-2015')
WHEN bmon='BudMnt6' THEN CONVERT(DATE, '01-JUN-2015')
WHEN bmon='BudMnt7' THEN CONVERT(DATE, '01-JUL-2015')
WHEN bmon='BudMnt8' THEN CONVERT(DATE, '01-AUG-2015')
WHEN bmon='BudMnt9' THEN CONVERT(DATE, '01-SEP-2015')
WHEN bmon='BudMnt10' THEN CONVERT(DATE, '01-OCT-2015')
WHEN bmon='BudMnt11' THEN CONVERT(DATE, '01-NOV-2015')
WHEN bmon='BudMnt12' THEN CONVERT(DATE, '01-DEC-2015')
END AS bmon from tablename
UNPIVOT
(
       budget
       FOR bmon IN (BudMnt1,
BudMnt2,
BudMnt3,
BudMnt4,
BudMnt5,
BudMnt6,
BudMnt7,
BudMnt8,
BudMnt9,
BudMnt10,
BudMnt11,
BudMnt12)
) p

Any help would be much appreciated ?

`CONVERT(DATE, '01-JAN-2015')` seems to be a plain date constant, so that would be `DATE '2015-01-01'` in standard SQL and Postgres. But Postgres has no `UNPIVOT` clause. There is also no `uncrosstab()` function for Postgres - where in the manual did you find that? — , Oct 23 '15 at 10:42
We found postgres refrences here http://www.postgresql.org/docs/9.1/static/tablefunc.html,http://www.postgresql.org/message-id/C237184E7081314392F31AE826947FFA44E3A56E11@EXWA-MBX01.nexus.csiro.au — NEO, Oct 23 '15 at 10:48
There is a `crosstab` function indeed. But no **un**crosstab() — , Oct 23 '15 at 10:58
Ok, We will make try on crosstab() function instead of unpivot then let you final outcome here. thanks for help you gave us . — NEO, Oct 23 '15 at 11:00
We are using crosstab() function here. getting following error :ERROR: function crosstab(unknown) does not exist LINE 1: select * from crosstab('select Name,budget,ct from tablename' HINT: No function matches the given name and argument types. You may need to add explicit type casts. — NEO, Oct 23 '15 at 11:28
We are using following build of postgres : =# select version(); version ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ 1.3.0.2 build 14421) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 18 2015 17:03:43 (1 row) — NEO, Oct 23 '15 at 11:31
Is crosstab functionality does not exist in postgres 8.2 version ? So, what could that be? Advance Thanks for any hints! — NEO, Oct 23 '15 at 11:47
Do we have possibility of decoding case when then using standard sql for data transposing here instead of crosstab() functionality — NEO, Oct 23 '15 at 14:25

Jon Roberts · Answer 1 · 2015-10-26T19:19:15.800

PIVOT and UNPIVOT are non-ANSI standard SQL commands that I am not familiar with. I also don't have your full table definition so I am having a hard time just understanding your SQL. So, I turned to Microsoft's site and found an example to work with.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Using their UNPIVOT example, I came up with two solutions. First, let's create a table in Greenplum and insert some values.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int)
distributed by (vendorid);

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);

The unsupported Microsoft UNPIVOT example:

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Solution 1

select vendorid, 'emp1' as employee, emp1 from pvt
union all
select vendorid, 'emp2' as employee, emp2 from pvt
union all
select vendorid, 'emp3' as employee, emp3 from pvt
union all
select vendorid, 'emp4' as employee, emp4 from pvt
union all
select vendorid, 'emp5' as employee, emp5 from pvt;

This is simple and easy to write but each query is run sequentially. It also means you are scanning the pvt table 5 times but it might be the right solution for you.

Solution 2

select vendorid, 
       split_part(unpivot, ',', 1) as employee, 
       split_part(unpivot, ',', 2) as orders
from    (
        select vendorid, unnest(employee) as unpivot
        from    (
                select vendorid, 
                       array['emp1,' || emp1, 
                             'emp2,' || emp2, 
                             'emp3,' || emp3, 
                             'emp4,' || emp4, 
                             'emp5,' || emp5] as employee 
                from pvt
                ) as sub
        ) as sub2;

Solution 2 is a little "cooler" as it passes through the pvt table only once and converts the distinct columns into array elements. I concatenated the name of the column so I could have a index to each array element. I then used unnest to convert the array elements to rows. Finally, I used split_part to split each array index + value into separate columns. For fun, I called this "unpivot". :)

Lastly, I suggest you store the data so that is easy to use. That recommendation is valid for any database too. If you often want to see the data that is the result of UNPIVOT, store the data that way. Don't make analysis harder than it should be.

How do We Transform sql server 2012 query syntax into postgresql or standard sql

1 Answers1