How to transpose multiple columns into multiple rows?

Question

SQL

It's hard to explain, I'll show you with an example.
There are several fields with dates in the table and they correspond to fields with values, but I don’t need separate columns in the output for each type of values ​​(val1, val2, etc.), I need one column with the name of the value and one with values , therefore, I wrote a request:

select val1_date as date, 'val1' as val_name, count(val1) as value
from ... 
group by 1,2
union
select val2_date as date, 'val2' as val_name, count(val2) as value
from ... 
group by 1,2

We get accordingly:

And the question is actually how to write it normally, without using a union, in one request.

Answer 1

It's called PIVOT: hp+vertica+pivot .
For example, a calendar report , in MS SQL.

table with fields: id, month_id, kpi1, kpi2
to become: id, kpi1-2020-01-01, kpi1-2020-02-01, kpi2-2020-01-01, kpi2-2020-02-01.

WithoutPIVOT

SELECT ID, SUM(M1K1), SUM(M2K1), SUM(M1K2), SUM(M2K2) FROM
(SELECT ID, kpi1 AS M1K1, 0 AS M2K1, kpi2 AS M1K2, 0 AS M2K2 FROM Table_K WHERE month_id = 1
UNION
SELECT ID, 0, kpi1, 0, kpi2  FROM Table_K WHERE month_id = 2) AS DailyData
GROUP BY ID