时间戳

首页 > 解决方案 > 将分组数据拆分为单独的列 SQL

sql - 将分组数据拆分为单独的列 SQL

问题描述

我有一张这样的桌子

Project    Cost    Cost Type 
---------------------------
Project 1  100k     Internal
Project 1  12k      External
Project 2  45k      Internal
Project 2  2k       External
Project 2  33k      Internal
Project 3  42k      External
Project 4  57k      Internal
Project 5  22k      Internal
Project 5  17k      External
------------------------------------------

但我需要的是这样的结果

Project    InternalCost     ExternalCost    Total Cost
---------------------------------------------------
Project 1   100k              12k             112k
Project 2   78k               2k               80k
Project 3    0k              42k               42k
Project 4   57k                0k              57k
Project 5   22k               17k              39k
--------------------------------------------------------

我的以下查询只是分组。但是我怎样才能超越这样的结果

select project,sum(cost) from project group by project,costtype

标签： sqlsql-server

解决方案

使用枢轴

;WITH CTE(Project,Cost,CostType)
AS
(
SELECT 'Project 1', '100k' ,'Internal' UNION ALL
SELECT 'Project 1', '12k'  ,'External' UNION ALL
SELECT 'Project 2', '45k'  ,'Internal' UNION ALL
SELECT 'Project 2', '2k'   ,'External' UNION ALL
SELECT 'Project 2', '33k'  ,'Internal' UNION ALL
SELECT 'Project 3', '42k'  ,'External' UNION ALL
SELECT 'Project 4', '57k'  ,'Internal' UNION ALL
SELECT 'Project 5', '22k'  ,'Internal' UNION ALL
SELECT 'Project 5', '17k'  ,'External' 
)
SELECT Project,
        ISNULL([Internal],0) AS [Internal],
        ISNULL([External],0) AS [External],
        CAST(SUM(ISNULL([Internal],0)+ISNULL([External],0))OVER(PARTITION BY Project ORDER BY Project) AS VARCHAR(100))+'K' AS TotalCost
FROM
(
SELECT Project,
        CAST(REPLACE(Cost,'k','') as int) as Cost,
        CostType
from CTE
)AS SRC
PIVOT
(
SUM(Cost) FOR CostType IN ([Internal],[External])
)
AS PVT

结果

Project     Internal    External    TotalCost
---------------------------------------------
Project 1       100         12          112K
Project 2       78          2           80K
Project 3       0           42          42K
Project 4       57          0           57K
Project 5       22          17          39K

推荐阅读