CODEWITHSUNDEEP
sqlsql-server
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Splitting Grouped Data as separate columns SQL

I have a table like this

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
------------------------------------------

But what I need is a result like this

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
--------------------------------------------------------

My following query is only grouping. But How can I get above such a result

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

Upvotes: 0

Views: 70

Answers (4)

Vikram Singh
Vikram Singh

Reputation: 124

enter image description here 

 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]

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Using Pivot

;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

Result

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

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use following query:

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation :

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;

Upvotes: 0

Related Questions