This can be achieved using the SQL Server PIVOT function.
The PIVOT function will allow you to perform any aggregation type on a particular object, such as counts, sums, averages etc
It has some limitations in that you need to know what values would be returned in the rows, this means that it is possible to not see the true picture of the data.
This limitation comes about because you need to name each column when pivoting the data.
The PIVOT function is called at the end of a query once you have selected the columns and added any joins and where clauses.
The syntax is
PIVOT(’aggregation type'(’Object to be aggregated’) FOR ’Object aggregation is for’ IN (List of values which will be returned) )
Example
We have some episodes, there ID’s and the Months in which they were completed. We need a table to show each unique Episode and the months they were completed with a count across the months.
Aggregation Type = COUNT
Object to be aggregated = Episode_ID
Object aggregation is for = Month
List of Values = Jan,Feb,Mar,Apr etc (These are what we expect to be returned)
Therefore the full SQL would be:
SELECT Episode_Type,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
FROM TableA
PIVOT(COUNT(Episode_ID) FOR Month IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) AS PVT
As you can see we have had to manually name the columns as well as specifying the Months we are expecting to see.
The result of the above SQL would be a table that had a row for each Episode a column for each month and a number in each cell which was the number of episodes of that type in that month.
If you have many where clauses it might be worth using the with command to specify your table beforehand this would neaten up the SQL for the PIVOT.