Wednesday, January 18, 2012

Using Pivot to count data in different columns

Hi All,

first of all i would like to wish a happy new year and then goes the first tip, in SQL you might get caught in a situation in which you need to Group on one column and showing the counts of other columns for each group element and when this situation arose PIVOT comes to scene.

I'll be showing one simple example in which ill be counting different values for each column.

SELECT isnull(value,'No Name') 'Column Name', Value1 , Value2,  
Value3
FROM
(
SELECT u.GroupByColumnName 'value',ColumnNameContainingValues
FROM SQLTable
) p
PIVOT
(
COUNT (statuscode)
FOR statuscode
IN (Value1 , Value2,  
Value3)
) AS pvt;



and you will get the required results.

Happy Coding...

No comments: