Как транспонировать строки в столбцы?

Question

[Dmitry]

У меня есть 3 таблицы такого вида

spoiler

CREATE TABLE dep
(
    id   integer NOT NULL,
    city text    NOT NULL,
    name text    NOT NULL,
    CONSTRAINT dep_pk PRIMARY KEY (id)
);

-- продукты

CREATE TABLE prod
(
    id    integer NOT NULL,
    price integer NOT NULL,
    name  text    NOT NULL,
    CONSTRAINT prod_pk PRIMARY KEY (id)
);

-- продажи

CREATE TABLE sales
(
    time    timestamp    NOT NULL,
    dep_id  integer        NOT NULL,
    prod_id integer        NOT NULL,
    cost    numeric(10, 2) NOT NULL,
    CONSTRAINT sales_fk1 FOREIGN KEY (dep_id) REFERENCES dep (id),
    CONSTRAINT sales_fk2 FOREIGN KEY (prod_id) REFERENCES prod (id)
);

commit;

Я написал запрос выводящий информацию в таком виде year, month, sum(cost)

spoiler

select EXTRACT(YEAR FROM time) AS YEAR, EXTRACT(month FROM time) AS month,SUM(cost) as сумма_продаж 
FROM	 
		dep
		INNER JOIN prod ON prod.id = dep.id
		INNER JOIN sales ON prod.id = sales.prod_id
WHERE time >= '2018-01-01 00:00:00'
AND city = 'town1'
GROUP BY year, month

Мне нужно перевести это в такой вид
--год \ месяц 1 2 3 4 5 6 7 8 9 10 11 12
----------------------------------------
--2018 x x x x x x x x x x x x
--2019 x x x x x x x x x x x x
--2020 x x x x x x x x x x x x
х - это сумма, которая сейчас у меня выводится в 3 столбце

Comments

[Akina]

CROSSTAB()
Хотя гораздо разумнее делать такую трансформацию на стороне клиента.

Либо сразу получать нужную форму условным агрегированием.

[Dmitry]

Akina, у меня не получается сделать в таком виде при помощи crosstab, можете, пожалуйста на моем примере показать.
Пробовал вот так

crosstab(select EXTRACT(YEAR FROM time) AS year, EXTRACT(month FROM time) AS month,SUM(cost) as sum_cost 
FROM	 
		dep
		INNER JOIN prod ON prod.id = dep.id
		INNER JOIN sales ON prod.id = sales.prod_id
WHERE time >= '2018-01-01 00:00:00'
AND city = 'town1'
GROUP BY year, month
ORDER BY year, month 
)as itog_table(year int,1 int,2 int, 3 int,4 int,5 int,6 int,7 int,8 int,9 int,10 int,11 int,12 int);

[Дмитрий]

Три запроса на каждый год и union не получится?

[Dmitry]

Дмитрий, можно так сделать, но вопрос тот же, как привести именно к такому виду как в примере который я дал выше? т.е месяцы(1,2,3..12) нужно транспонировать в столбцы сохранив при этом сумму продаж, а первый столбцом чтоб шел год

[Дмитрий]

Dmitry, проще наверное на клиенте собрать. Запросом это будет явно медленнее

[Dmitry]

Дмитрий, задание у меня такое) именно запросом нужно

[Дмитрий]

Dmitry, придётся изобретать велосипед, может даже с временными таблицами

Answer 1

[Melkij]

проще на клиенте форматировать. Но поскольку число столбов в этой задаче можно считать константой, то можно и захардкодить

select EXTRACT(YEAR FROM time) AS year,
sum(cost) filter(where EXTRACT('month' FROM time) = 1) as jan,
sum(cost) filter(where EXTRACT('month' FROM time) = 2) as feb,
...
sum(cost) filter(where EXTRACT('month' FROM time) = 12) as dec
from tablename
where ...
group by 1
order by 1

Answer 2

[alexalexes]

Никак. Эта форма вывода сведений. Она должна рисоваться либо у конечного пользователя в графическом интерфейсе, либо скриптом на стороне сервера, клиентом СУБД которого он является (PHP, NodeJS), либо, на худой конец, хранимой процедурой или функцией, которая на выходе выдает текст в html.
Напрягать SQL, чтобы выводил произвольное кол-во столбцов - это не его задача.

Answer 3

[Константин Цветков]

POSTGRESQL+PIVOT