Is it possible to make such a request?

Question

SQL

Hello, how can I make a query so that as a result a table similar to this one appears:

spoiler

the number of rivers, as I understand it, can be substituted through a construction like WHERE IN(
How then to be with the columns? The number of carriers will also change depending on the needs.

Answer 1

pivot

Answer 2

When you know the possibilities of the dialect well enough, everything is easy and just
the TransactSQL dialect

DECLARE @mass = (SELECT SUM(value) FROM transact)
SELECT t.river, 
SUM(CASE WHEN t.driver IN (1,2,3)THEN t.value ELSE 0 END) AS 'pervochik1',
SUM(CASE WHEN t.driver IN (4,5,6)THEN t.value ELSE 0 END) AS 'pervochik2',
SUM(CASE WHEN t.driver IN (7,8,9)THEN t.value ELSE 0 END) AS 'pervochik3',
SUM(t.value) AS 'itog',
p.value AS 'plan',
SUM(t.value)/@mass AS 'procent'
FROM transacts t
LEFT JOIN plan p ON p.river = t.river
GROUP BY t.river, p.value

Answer 3

How to make a query depends on how the data is stored in the database.
And if you wanted to ask how to store it in the database, then if the number of rivers and carriers can be any, then you can use the Attribute Entity Value model. But it is difficult to collect data according to such a model, and with an indefinite number of columns, you will need to dynamically collect the query. As already written, pivot is used for this, this is a very expensive operation, so if there is an opportunity it is better to consider other options.