Получить определенные данные из разных таблиц для каждой даты

Question

У меня две таблицы

• X с идентификаторами столбцов, датой и количеством

• другая таблица - Y с id столбцов, кодом.

Теперь я хочу получить результаты в упомянутой форме, чтобы в разных столбцах были разные столбцы: code, id и count в разные даты.

TABLE X
        id date         count
        1  2016-08-16     20
        1  2016-08-17     34
        1  2016-08-18     56
        1  2016-08-19     67
        1  2016-08-20     87

TABLE Y
id  code
1    101
2    102
3    103
4    104
5    105

RESULT
        CODE    ID   2016-08-16 2016-08-17 2016-08-18  2016-08-19  2016-08-20                                    
        101       1  20         34         56          67          87

Answer 1

1. Простой способ получить аналогичный результат

   SELECT y.code, y.id, json_object_agg(x.date, x.count)
   FROM x INNER JOIN y ON x.id = y.id GROUP BY y.id, y.code
   

   Если вы используете другой язык для обработки результата запроса, я думаю, что он достаточно хорош.

2. Если столбцы даты должны быть динамическими, я думаю, что PL/pgSQL необходим.

   DO $$
   BEGIN
       EXECUTE (SELECT format('CREATE TEMPORARY TABLE result AS
           SELECT code, id, %s FROM (
               SELECT y.code, y.id, json_object_agg(x.date, x.count) AS counts
               FROM x INNER JOIN y ON x.id = y.id GROUP BY y.id, y.code) as sub',
               string_agg(DISTINCT 'counts->>''' || date :: TEXT || ''' AS ' || '"' || date :: TEXT || '"', ',')
           ) FROM x);
   END
   $$ LANGUAGE plpgsql;
   SELECT * FROM result;