Pivot Multiple columns together in SQL

Question

I've table like this

I need to get a result like this

RT       Team     PI     Committed   Done-Partial   Done-Full
----------------------------------------------------------------
ART1     Team1     10       5           1               3
ART2     Team2     7        5           4               1
-----------------------------------------------------------------------

The way I tried is as follows

;with RecentPI as(
select * from  (
select rt,Team,pi,[Finish Date],DENSE_RANK() over( partition by rt order by [Finish Date] desc) PIRank from Schedule_Manual S inner join TFS_ARTs_Teams T on T.ART=S.RT

group by RT,Team,PI,[Finish Date]
)tbl

 where PIRank=1
 )

 select * from (select  Obj.RT,Obj.[TFS Team], Obj.Type,Obj.PI,[PI Status] from Objectives Obj inner join RecentPI RP on RP.RT=Obj.RT and RP.Team=Obj.[TFS Team] and RP.PI=Obj.PI) as query 
 PIVOT (count(type) for [Type] in ([Committed])) p1
  PIVOT (Count([PI Status]) for [pi status] in ([Done-Partial],[Done-Full])) p2

But it doesnt seems to be correct and also Im not getting full columns in the query. Sorry Im very beginner with SQL Pivot

I think this needs to be a dynamic pivot to get the column names. See.. https://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns — SS_DBA, Sep 05 '19 at 13:05
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Popeye, Sep 05 '19 at 13:09
@Tejash Are you sure since there are two columns and its values to be considered — Sandeep Thomas, Sep 05 '19 at 13:13

score 2 · Answer 1 · answered Sep 05 '19 at 13:15

You can Use this simple Query...your problem will be solved....

select RT,Team,[PI],
 sum(case when [Type] = 'Committed' then 1 else 0 end) AS 'Committed',
  sum(case when PIStatus = 'Done-Partial' then 1 else 0 end) AS 'Done-Partial',
  sum(case when PIStatus = 'Done-Full' then 1 else 0 end) AS 'Done-Full'
from tbl_Pivot
group by RT,Team,[PI]

Output:-

Pivot Multiple columns together in SQL

1 Answers1