sqlpostgresqlpivotcrosstab

Postgresql PIVOT

I'm pretty new in the world of sql queries and would appreciate help with pivoting. I have a table similar to this one:

Table 1

That I want to turn into this:

Table 2

I bet it is a pretty straightforward process but still any help would be appreciated.

Solution

  • SQL Fiddle

    PostgreSQL 9.6 Schema Setup:

    CREATE TABLE MyTable (Description varchar(255), Label varchar(255),Val varchar(255));
INSERT INTO MyTable (Description,Label,Val) VALUES ('Name1','Location','Europe')
,('Name1','Depth','1200'),('Name1','Date','24.2.2011'),('Name2','Location','Australia')
,('Name2','Depth','3233'),('Name2','Date','1.1.1999'),('Name3','Location','Africa')
,('Name3','Depth','1323'),('Name3','Date','15.2.2018')

    Query 1:

    with CTE AS (select *,
(CASE WHEN Label='Location' THEN Val  END) AS Location,
(CASE WHEN Label = 'Depth' THEN Val END) AS Depth,
(CASE WHEN Label='Date' THEN Val END) AS Dates,
ROW_NUMBER() OVER (PARTITION BY Label,Val Order By Description) as rn
from MyTable
group by Label,Description,Val              )

select c.Description
,max(c.Location) AS Location
,max(c.Depth) AS Depth
,max(c.Dates) AS Dates
from cte c
where rn=1
group by c.Description 
order by c.Description

    Results:

    | description |  location | depth |     dates |
|-------------|-----------|-------|-----------|
|       Name1 |    Europe |  1200 | 24.2.2011 |
|       Name2 | Australia |  3233 |  1.1.1999 |
|       Name3 |    Africa |  1323 | 15.2.2018 |