SQL Pivot Table

Question

I have a table like this;

var1    var2 var3 cats

a      b    b       cat1
        b     b       cat1
a                      cat2
a      a    a       cat3
a      a    a       cat2

and want to get pivot table. I want to count a and b with the category of vars and cats.I am an excel user. I've looked other samples but could not solve this... thanks for help

vars    cats a b

var1    cat1 1 
var1    cat2 2 
var1    cat3 1 
var2    cat1  2
var2    cat2 1 
var2    cat3 1 
var3    cat1  2
var3    cat2 1 
var3    cat3 1

Answer 1

If you are using Sql Server 2005 yuo can make use of the UNPIVOT

DECLARE @Table TABLE(
     var1 VARCHAR(10),
     var2 VARCHAR(10),
     var3 VARCHAR(10),
     cats VARCHAR(10)
)


INSERT INTO @Table SELECT 'a','b','b','cat1'
INSERT INTO @Table SELECT null,'b','b','cat1'
INSERT INTO @Table SELECT 'a',null,null,'cat2'
INSERT INTO @Table SELECT 'a','a','a','cat3'
INSERT INTO @Table SELECT 'a','a','a','cat2'

SELECT  MyVars,
     cats,
     SUM(CASE WHEN Vars = 'a' THEN 1 ELSE 0 END) A,
     SUM(CASE WHEN Vars = 'b' THEN 1 ELSE 0 END) B
FROM    (
      SELECT *
      FROM (
         SELECT cats,
           var1,
           var2,
           var3
         FROM @Table
        )p
      UNPIVOT (Vars FOR MyVars IN (var1, var2,var3)) AS up
     ) sub
GROUP BY MyVars,
     cats
ORDER BY 1, 2