SQL | View Column as Multiple Columns Based on Conditions

Question

Newbie Postgresql (9.6.6) question here :)

I want to create a View that will split a single column into several columns, based on different conditions.

Example Table

  Name    Score   Season  
 ------- ------- -------- 
  John       12   Fall    
  John       15   Winter  
  John       13   Spring  
  Sally      17   Fall    
  Sally      10   Winter  
  Sally      14   Spring  
  Henry      16   Fall    
  Henry      12   Winter  
  Henry      18   Spring  

I want the View to dislay something that looks like this:

  Name    Fall Score   Winter Score   Spring Score  
 ------- ------------ -------------- -------------- 
  John            12             15             13  
  Sally           17             10             14  
  Henry           16             12             18  

Where the "Score" field is broken out into several different columns, each one populated based on WHERE clause that references the "Season" field. I've looked into both Window Functions and CASE Statements for accomplishing this purpose, but haven't been successfully thus far.

Any help is greatly appreciated!

Answer 1

Selecting from the entire table while grouping over the Name and then conditionally SUMming over the Score column will work:

SELECT
    "Name",
    SUM(CASE WHEN "Season" = 'Fall' THEN "Score" ELSE 0 END) AS "Fall",
    SUM(CASE WHEN "Season" = 'Winter' THEN "Score" ELSE 0 END) AS "Winter",
    SUM(CASE WHEN "Season" = 'Spring' THEN "Score" ELSE 0 END) AS "Spring"
FROM "mytable"
GROUP BY "Name"

Whether or not you use SUM() is up to you and how your data looks. If you have one row per (Name, Season) pair then SUM() will work equally as well as MAX()

Answer 2

You need a pivot table:

On SQL server you can do something like this example (hope it's the same for postgress), in others versions of SQL exist the pivot relational operators, but I'm not sure if Pivot works on Postgres

Example:

CREATE TABLE #Table
(
   Name nvarchar(400),
   Score int,
   Season nvarchar(400)
)

insert into #Table values ( 'John ',12,'Fall')
insert into #Table values ( 'John ',15,'Winter'   )
insert into #Table values ( 'John ',13,'Spring'   )
insert into #Table values ( 'Sally',17,'Fall   '  )
insert into #Table values ( 'Sally',10,'Winter'   )
insert into #Table values ( 'Sally',14,'Spring'   )
insert into #Table values ( 'Henry',16,'Fall'    ) 
insert into #Table values ( 'Henry',12,'Winter'   )
insert into #Table values ( 'Henry',18,'Spring'  )

select 
    c.Name
    ,sum(c.[Fall Score]) as [Fall Score]
    ,sum(c.[Winter Score]) as [Winter Score]
    ,sum(c.[Spring Score]) as [Spring Score]
from
(SELECT 
    t.name,
    case 
        when t.Season = 'Fall' then t.Score
        when t.Season = 'Winter' then 0
        when t.Season = 'Spring' then 0
    end as [Fall Score],
    case 
        when t.Season = 'Fall' then 0
        when t.Season = 'Winter' then t.Score
        when t.Season = 'Spring' then 0
    end as [Winter Score],
    case 
        when t.Season = 'Fall' then 0
        when t.Season = 'Winter' then 0
        when t.Season = 'Spring' then t.Score
    end as [Spring Score]
from #Table t
)as c 
group by c.name