I'm pretty new in the world of sql queries and would appreciate help with pivoting. I have a table similar to this one:
That I want to turn into this:
I bet it is a pretty straightforward process but still any help would be appreciated.
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
| description | location | depth | dates |
|-------------|-----------|-------|-----------|
| Name1 | Europe | 1200 | 24.2.2011 |
| Name2 | Australia | 3233 | 1.1.1999 |
| Name3 | Africa | 1323 | 15.2.2018 |