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.. stackoverflow.com/questions/18657214/… — SS_DBA
– SS_DBA, Commented Sep 5, 2019 at 13:05
Possible duplicate of Convert Rows to columns using 'Pivot' in SQL Server — Popeye
– Popeye, Commented Sep 5, 2019 at 13:09
@Tejash Are you sure since there are two columns and its values to be considered — Sandeep Thomas
– Sandeep Thomas, Commented Sep 5, 2019 at 13:13

Akhileshkumar singh · Accepted Answer · 2019-09-05 13:15:48Z

2

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

answered Sep 5, 2019 at 13:15

Akhileshkumar singh

1,5322 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pivot Multiple columns together in SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related