代码之家 › 专栏 › 技术社区 › Sandeep Thomas

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

sql-server sql

Sandeep Thomas · 技术社区 · 7 年前

我有一张这样的桌子

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

4 回复 | 直到 7 年前

Sreenu131 7 年前

使用枢轴

;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

Vikram Singh 7 年前

declare@table table(project varchar(20),cost varchar(20),[cost type]varchar(20))
插入@table(项目,成本,[成本类型])
选择“项目1”、“100K”、“内部”联合
选择“项目1”、“12K”、“外部”联合
选择“项目2”、“45K”、“内部”联合
选择“项目2”、“2K”、“外部”联合
选择“项目2”、“33K”、“内部”联合
选择“项目3”、“42K”、“外部”联合
选择“项目4”、“57K”、“内部”联合
选择“项目5”、“22K”、“内部”联合
选择“项目5”、“17K”、“外部”

选择项目,isNull([内部],'0K')[内部成本],isNull([外部],'0K')[外部成本]
,cast(isNull(cast(replace([内部]、'K'、'')as int)+cast(replace([外部]、'K'、'')as int),0)as varchar(30))+'K'[总成本]
从
(
选择项目,成本,[成本类型]
从@表
D
枢轴
(最大值(成本))
对于中的[成本类型]([内部]、[外部])
PIV![在此处输入图像描述][1]][1]
< /代码> 


 DECLARE @table TABLE (Project  VARCHAR(20),Cost      VARCHAR(20) ,[Cost Type]  VARCHAR(20)  )
    INSERT into @table(Project,Cost,[Cost Type])
    Select 'Project 1', '100k',     'Internal' Union
    Select 'Project 1',  '12k',      'External'Union
    Select 'Project 2',  '45k',      'Internal'Union
    Select 'Project 2',  '2k',       'External'Union
    Select 'Project 2',  '33k',      'Internal'Union
    Select 'Project 3',  '42k',      'External'Union
    Select 'Project 4',  '57k',      'Internal'Union
    Select 'Project 5',  '22k',      'Internal'Union
    Select 'Project 5',  '17k',      'External'

    select Project,ISNULL([Internal],'0k')[InternalCost],ISNULL([External],'0k') [ExternalCost]
    ,Cast(ISNULL(cast(Replace([Internal],'k','')as Int)+cast(Replace([External],'k','')as Int),0)as varchar(30))+'k' [Total Cost] 
    from
    (
      select Project,Cost,[Cost Type]
      from @table 
    ) d
    pivot
    ( max(cost)
      for [Cost Type] in ([Internal],[External])  
    ) piv [![enter image description here][1]][1]

Yogesh Sharma 7 年前

你可以做到 有条件的 聚合:

select project,
       sum(case when costtype = 'Internal' then Cost else 0 end) as Internal,
       sum(case when costtype = 'External' then Cost else 0 end) as External,
       sum(case when costtype in ('Internal','External') then Cost else 0 end) as TotalCost
from project p
where costtype in ('Internal','External')
group by project;

Fahmi 7 年前

使用以下查询:

select project,
sum(case when costtype='Internal' then cost else null end) as InternalCost,
sum(case when costtype='External' then cost else null end) as ExternalCost,
sum(case when costtype='Internal' then cost else null end)+sum(case when costtype='External' then cost else null end) as TotalCost
from project group by project