Sample of Pivot SQL

Tuesday, 06 April 2010 14:16 Ajay Majgaonkar Software Development - SQL

As a part of reporting, my friend faced a simple looking problem. He was struggling for almost half a day to solve this by a SQL query. When he showed me the problem, I just realized that it is something that we usually do in excel called pivot tables and I asked him to look over the internet if there is something called "pivot query" and to our surprise there is something called as that...we just didn't know, so I am going to document it. Here is a table we were looking at -
TestId   TestArea   TestResult
1                A           Pass
2                B           Fail
3                A           Pass
4                B           Pass
5                A           Fail

What we wanted as output was -
TestArea   Pass   Fail
A             2         1
B             1         1

So, write a query to group on testarea then create 2 colums from the testresult column and populate the values by counting passed and failed tests. Looked very simple to me and I tried to create it using inner joins but no luck.

The solution is to use Pivot Query as below -


SELECT TestArea, PASS, FAIL
FROM (
SELECT TestResult, TestArea, TestID
FROM TestResultTable1) up
PIVOT (COUNT(TestID) FOR TestResult IN (PASS, FAIL)) AS pvt