SQL - CASE WHEN - данные отображаются в отдельных строках после транспонирования

Я пытаюсь перенести данные столбца в отдельные столбцы. (Обратите внимание, что я не могу использовать функцию Pivot или динамический SQL).

Исходная таблица имеет три столбца (год, город, данные1).

Я пытаюсь перенести столбец «город» в отдельные столбцы с целью сравнения данных для выбранных городов.

Вот мой код:

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

В результате получается следующая таблица.

вывод

Проблема в том, что строка «год» дублируется несколько раз, а я бы хотел, чтобы данные отображались в одной строке «год» для всех выбранных городов. В противном случае я не могу построить линейный график.

Любая помощь приветствуется.

Пример ввода таблицы:

+------+---------------+-------+
| 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 |
+------+---------------+-------+

Выходная таблица после использования CASE WHEN или 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 |
+------+-------+-------+-------+

Желаемый формат вывода (все данные за год отображаются в одной строке):

+------+-------+-------+-------+
| 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 |
+------+-------+-------+-------+

sql transpose multiple-columns case-when
person euclideans    schedule 18.09.2018    source источник
comment
Что вы ожидаете, когда есть несколько данных1 за год и город? Сумма, наверное?   -  person Clay    schedule 19.09.2018
comment
Какие у вас dbms?   -  person D-Shih    schedule 19.09.2018

Ответы (2)


arrow_upward
4
arrow_downward

Вы можете попробовать использовать агрегатную функцию условия

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

Примечание

Если есть несколько data1 на city, вы можете попробовать использовать SUM вместо MAX

person D-Shih    schedule 18.09.2018
comment
результаты по-прежнему показывают данные1 за один и тот же год 3 раза (пример: 1750 SF data1a, 1750 NYC data1b, 1750 PAR data1c). По сути, я хотел бы иметь данные1 для каждого города только в одной строке «год». Чтобы уточнить, я не пытаюсь суммировать данные1, поскольку каждая строка в столбце данных1 отличается друг от друга. - person euclideans; 19.09.2018
comment
@euclideans Не могли бы вы предоставить некоторые образцы данных и ожидать результата? - person D-Shih; 19.09.2018
comment
Я отредактировал вопрос, чтобы добавить образцы входных и выходных таблиц. - person euclideans; 19.09.2018
comment
Где ваш год 2003 и 2004 в данных выборки? - person D-Shih; 19.09.2018
comment
И мой запрос работает хорошо db-fiddle.com/f/5ZTm3uSVyNC1434pMMFEWr/0 Мой столбец group by отличается от вашего. Я только GROUP BY t.year - person D-Shih; 19.09.2018
comment
Я просто добавил данные за 2003, 2004, 2005 годы. - person euclideans; 19.09.2018
comment
Да! Он работал с вашим скриптом db-fiddle. Я не уверен, почему я не смог воспроизвести ваш SO-скрипт. Спасибо! - person euclideans; 19.09.2018

arrow_upward
2
arrow_downward

Вы можете попробовать использовать функцию суммы

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
person Shereen    schedule 18.09.2018
comment
В чем разница между вашим ответом и моим? - person D-Shih; 16.04.2021
comment
группировать по предложению - person Shereen; 20.04.2021