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

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?

CodePudding user response：

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'

CodePudding user response：

You can try using union all like the following query.

;WITH cte
AS (
    SELECT Project
        ,'Expected' AS Costs
        ,DevCostsExpected AS Dev
        ,SalesCostsExpected AS Sales
    FROM ProjectCosts
    
    UNION ALL
    
    SELECT Project
        ,'Target' AS Costs
        ,DevCostsTarget AS Dev
        ,SalesCostsTarget AS Sales
    FROM ProjectCosts
    
    UNION ALL
    
    SELECT Project
        ,'Actual' AS Costs
        ,DevCostsActual AS Dev
        ,SalesCostsActual AS Sales
    FROM ProjectCosts
    )
SELECT *
FROM cte
WHERE Project = 'A'