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 |