pivoting against two columns

Question

Post reply

pivoting against two columns

xenophilia

SSCommitted

Points: 1891
More actions
April 14, 2012 at 4:45 pm

#392026

I have a working pivot query. I want to add two more columns to it, that also must be pivoted. I am struggling to get it to work.
--I need to add these two columns:
, COUNT(CASE WHEN Outcome IN ('Passed', 'Failed', 'Blocked') THEN outcome END) AS TotalExecuted
,COUNT(outcome) AS TotalPlanned
--to this existing query (which works)
select *
from
(Select Distinct outcome,DTS.SuitePath,FTR2.TestCaseId
from dbo.FactTestResult FTR
Join DimTestResult TR on FTR.ResultSK = TR.ResultSK
Join CurrentWorkItemView WI
On FTR.TestCaseId = WI.System_Id
Join DimTestSuite DTS on FTR.TestSuiteSK = DTS.TestSuiteSK
inner Join(select TestSuiteSK,TestCaseId, Max(resultdate) as Max_ResultDate from dbo.FactTestResult
group by TestSuiteSK,testcaseid) FTR2
on FTR.TestCaseId = ftr2.TestCaseId
and FTR.ResultDate = FTR2.Max_ResultDate
where WI.Microsoft_VSTS_Common_Priority in (1,2,3,4,5)
and DTS.suitepath like '\17.2 - FY00 FQR Top Secret%'
)p
pivot(count([outcome]) for [outcome] in ([Blocked],[Failed],[Never Run],[Passed])) as pvt
When I remove the pivot operator from the above query and add the additional two lines after the first line of the query select statement returns results.
--this runs
Select Distinct outcome,DTS.SuitePath,FTR2.TestCaseId
, COUNT(CASE WHEN Outcome IN ('Passed', 'Failed', 'Blocked') THEN outcome END) AS TotalExecuted
,COUNT(outcome) AS TotalPlanned
from dbo.FactTestResult FTR
Join DimTestResult TR on FTR.ResultSK = TR.ResultSK
Join CurrentWorkItemView WI
On FTR.TestCaseId = WI.System_Id
Join DimTestSuite DTS on FTR.TestSuiteSK = DTS.TestSuiteSK
inner Join(select TestSuiteSK,TestCaseId, Max(resultdate) as Max_ResultDate from dbo.FactTestResult
group by testcaseid, TestSuiteSK) FTR2
on FTR.TestCaseId = ftr2.TestCaseId
and FTR.ResultDate = FTR2.Max_ResultDate
where WI.Microsoft_VSTS_Common_Priority in (1,2,3,4,5)
and DTS.suitepath like '\17.2 - FY00 FQR Top Secret%'
group by ftr2.testcaseid, dts.suitepath, outcome
However, I need this information to pivot.
Please will someone show me which lines to modify in my pivot statement so that it works with the additional two columns TotalExecuted and TotalPlanned?
-

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

PIVOT can work on one column at a time.

You could pivot manually using a crosstab[/url].

If you're looking for some help on the forums, I suggest including table scripts, sample data and expected output to your question. See the first link in my signature line for more information.

-- Gianluca Sartori

vinu512 SSCoach Points: 15729 More actions · Answer 2

This might help:

Pivot two or more columns in SQL Server

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

KoldCoffee

SSCoach

Points: 18971

April 16, 2012 at 6:31 pm

#1474479

Thank you very much for the links.

vinu512 SSCoach Points: 15729 More actions · Answer 4

You're welcome.

Always Happy to help.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉