Rearrange SQL-Output via SQL-Query kind of "transposition"

October 31, 2022

I would like to query project costs for a project using SQL query (MS SQL Server 2016) and later prepare them in a chart using SQL report.
The data is available in SQL in this form:

+---------+------------------+----------------+----------------+--------------------+------------------+------------------+
| Project | DevCostsExpected | DevCostsTarget | DevCostsActual | SalesCostsExpected | SalesCostsTarget | SalesCostsActual |
+---------+------------------+----------------+----------------+--------------------+------------------+------------------+
| A       |             1000 |           2000 |           1500 |               2000 |             3000 |             2500 |
| B       |             5000 |           7500 |          10000 |               8000 |            10000 |             3500 |
| C       |             1400 |           1400 |           1000 |               5400 |             6000 |             7500 |
+---------+------------------+----------------+----------------+--------------------+------------------+------------------+

I need an SQL query that gives me the data in this form:

select ??? from ProjectCosts where Project = 'A'

+---------+----------+-------+-------+
| Project |  Costs   |  Dev  | Sales |
+---------+----------+-------+-------+
| A       | Expected |  1000 |  2000 |
| A       | Target   |  2000 |  3000 |
| A       | Actual   |  1500 |  2500 |
| B       | Expected |  5000 |  8000 |
| B       | Target   |  7500 | 10000 |
| B       | Actual   | 10000 |  3500 |
+---------+----------+-------+-------+

How can I achieve such a kind of "transposition" with an SQL query?

>Solution :

You may try to UNPIVOT the rows using VALUES table value constructor and an additional APPLY operator:

Test data:

SELECT *
INTO ProjectCosts
FROM (VALUES  
   ('A', 1000, 2000,  1500, 2000,  3000, 2500),
   ('B', 5000, 7500, 10000, 8000, 10000, 3500),
   ('C', 1400, 1400,  1000, 5400,  6000, 7500)
) v (Project, DevCostsExpected, DevCostsTarget, DevCostsActual, SalesCostsExpected, SalesCostsTarget, SalesCostsActual)

Statement:

SELECT p.Project, a.*
FROM ProjectCosts p
OUTER APPLY (VALUES
   ('Expected', p.DevCostsExpected, p.SalesCostsExpected),
   ('Target',   p.DevCostsTarget,   p.SalesCostsTarget),
   ('Actual',   p.DevCostsActual,   p.SalesCostsActual)
) a (Costs, Dev, Sales)
WHERE p.Project = 'A'