SQL - CASE WHEN - Data displayed on separate rows after transposing

Question

I am trying to transpose column data into separate columns. (Note that I can not use the Pivot function or dynamic SQL).

The source table has three columns (year, city, data1).

I am trying to transpose the column 'city' into separate columns for the purpose of comparing the data for selected cities.

Here's my code:

SELECT  t.year, 
   CASE WHEN city = 'San Francisco' THEN t.data1 END) SF, 
   CASE WHEN city = 'Paris' THEN t.data1 END) PAR, 
   CASE WHEN city = 'New York' THEN t.data1 END) NYC
FROM t
WHERE city IN ('San Francisco', 'Paris', 'New York')
GROUP BY t.year, t.data1
ORDER BY t.year

The result is the following table.

output

The problem is that the row 'year' is duplicated several times, while I'd like the data to be displayed on one single 'year' row for all the cities selected. Otherwise I can not plot any line chart.

Any help appreciated.

Table Input Sample:

+------+---------------+-------+
| year |     city      | data1 |
+------+---------------+-------+
| 2001 | San Francisco | 15.25 |
| 2001 | New York      | 10.93 |
| 2001 | Paris         | 11.37 |
| 2002 | San Francisco |    15 |
| 2002 | Paris         | 11.87 |
| 2002 | New York      | 11.25 |
| 2003 | Paris         | 11.91 |
| 2003 | New York      |  9.84 |
| 2003 | San Francisco | 15.43 |
| 2004 | New York      | 10.39 |
| 2004 | San Francisco | 15.37 |
| 2004 | Paris         | 11.34 |
| 2005 | Paris         | 11.55 |
| 2005 | New York      | 10.68 |
| 2005 | San Francisco | 15.17 |
+------+---------------+-------+

Output Table after using CASE WHEN or MAX/SUM(CASE WHEN)

+------+-------+-------+-------+
| year |  sf   |  par  |  nyc  |
+------+-------+-------+-------+
| 2001 | 15.25 |       |       |
| 2001 |       |       | 10.93 |
| 2001 |       | 11.37 |       |
| 2002 |    15 |       |       |
| 2002 |       | 11.87 |       |
| 2002 |       |       | 11.25 |
| 2003 |       | 11.91 |       |
| 2003 |       |       |  9.84 |
| 2003 | 15.43 |       |       |
| 2004 |       |       | 10.39 |
| 2004 | 15.37 |       |       |
| 2004 |       | 11.34 |       |
| 2005 |       | 11.55 |       |
| 2005 |       |       | 10.68 |
+------+-------+-------+-------+

Desired Output Format (all data displayed per year on a single row):

+------+-------+-------+-------+
| year |  sf   |  par  |  nyc  |
+------+-------+-------+-------+
| 2001 | 15.25 | 11.37 | 10.93 |
| 2002 |    15 | 11.87 | 11.25 |
| 2003 | 15.43 | 11.91 |  9.84 |
| 2004 | 15.37 | 11.34 | 10.39 |
+------+-------+-------+-------+

Answer 1

You can try to use condition aggregate function

SELECT  t.year, 
   SUM(CASE WHEN city = 'San Francisco' THEN t.data1 END) SF, 
   SUM(CASE WHEN city = 'Paris' THEN t.data1 END) PAR, 
   SUM(CASE WHEN city = 'New York' THEN t.data1 END) NYC
FROM t
WHERE city IN ('San Francisco', 'Paris', 'New York')
GROUP BY t.year
ORDER BY t.year

Note

If there are multiple data1 on city you can try to use SUM instead MAX

Answer 2

You can try using sum function

SELECT  t.year1, 
            SUM((CASE WHEN city = 'San Francisco' THEN t.data1 END)) SF,
            SUM((CASE WHEN city = 'Paris' THEN t.data1 END)) PAR, 
            SUM((CASE WHEN city = 'New York' THEN t.data1 END)) NYC
FROM t
WHERE city IN ('San Francisco', 'Paris', 'New York')
GROUP BY t.year, t.data1
ORDER BY t.year