我想使用 SQL 查詢（MS SQL Server 2016）查詢專案的專案成本，然后使用 SQL 報告在圖表中準備它們。資料以這種形式在 SQL 中可用：
--------- ------------------ ---------------- ---------------- -------------------- ------------------ ------------------
| 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 |
--------- ------------------ ---------------- ---------------- -------------------- ------------------ ------------------
我需要一個 SQL 查詢，它以這種形式為我提供資料：
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 |
--------- ---------- ------- -------
如何使用 SQL 查詢實作這種“換位”？
uj5u.com熱心網友回復：
您可以嘗試UNPIVOT使用VALUES表值建構式和附加APPLY運算子來處理行：
測驗資料：
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)
陳述：
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'
uj5u.com熱心網友回復：
您可以嘗試使用union all 如下查詢。
;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'
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/523357.html
上一篇：無法確定標簽“<macroname="m3_comp_bottom_app_bar_container_color">?attr/colorSurface</ma