Aggregate function within Pivot table query using SQL Server 2008 R2

Question

I have a table as shown below with the details:

CREATE TABLE testrf
(
  cola INTEGER,
  colb VARCHAR(10)
)   

Inserting some data:

INSERT INTO testrf VALUES(1,'x')  
INSERT INTO testrf VALUES(1,'x')
INSERT INTO testrf VALUES(2,'x')
INSERT INTO testrf VALUES(3,'y')
INSERT INTO testrf VALUES(4,'y')
INSERT INTO testrf VALUES(5,'c')
INSERT INTO testrf VALUES(6,'c')
INSERT INTO testrf VALUES(7,'c')
INSERT INTO testrf VALUES(8,'d')
INSERT INTO testrf VALUES(3,'y')
INSERT INTO testrf VALUES(12,'M1')
INSERT INTO testrf VALUES(13,'L1')
INSERT INTO testrf VALUES(14,'C2')
INSERT INTO testrf VALUES(1,'c')
INSERT INTO testrf VALUES(1,'d')
INSERT INTO testrf VALUES(1,'L1')


SELECT * FROM testrf;

cola   colb
------------
1       x
1       x
2       x
3       y
4       y
5       c
6       c
7       c
8       d
3       y
1       c
1       d
12      M1
13      L1
14      C2
1       L1

Now I want to show pivot table for the above data for which I have written the query:

 DECLARE @cols NVARCHAR(MAX)
 DECLARE @SQL NVARCHAR(MAX)

 SELECT  @cols = STUFF ( (SELECT DISTINCT '],[' + v.colb                        
               FROM testrf AS v                                         
FOR XML PATH('')), 1, 2, '') + ']'   


SET @SQL = N'SELECT cola,TotalGroups,AvailableIn,'+ @cols +'
   FROM                     
   (SELECT  v.cola,v.colb,(select count(distinct colb) from testrf) TotalGroups,c.AvailableIn
       FROM testrf AS v  
       inner join
       (select cola,count(case when colb>=1 then 1 else 0 end) AS AvailableIn FROM testrf
       group by cola) c 
       on c.cola = v.cola                                                                                                  
    ) p                     
       PIVOT                
           (            
               count(colb)                        
               FOR colb IN ( '+ @cols + ' )                        
           ) AS pvt';      

    EXEC(@SQL)     

I will get this:

 cola TotalGroups AvailableIn  c    C2  d   L1  M1  x   y
 ----------------------------------------------------------
 1       7            5        1    0   1   1   0   2   0
 2       7            1        0    0   0   0   0   1   0
 3       7            2        0    0   0   0   0   0   2
 4       7            1        0    0   0   0   0   0   1
 5       7            1        1    0   0   0   0   0   0
 6       7            1        1    0   0   0   0   0   0
 7       7            1        1    0   0   0   0   0   0
 8       7            1        0    0   1   0   0   0   0
 12      7            1        0    0   0   0   1   0   0
 13      7            1        0    0   0   1   0   0   0
 14      7            1        0    1   0   0   0   0   0

Note: Please note the row number 1 and 3. In AvailableIn column values are 5 for 1st row where the available values for the columns are 4 only that is c,d,L1,x rest of all are zeros. And also row number 3 where AvailableIn is 2 where available values for the columns is only 1 that is y. I think am stuck in aggregate function within the pivot query.

SQL Fiddle-> http://sqlfiddle.com/#!3/d1acc/8

Answer 1

Are you sure there is a question?

INSERT INTO testrf VALUES(1,'x');
INSERT INTO testrf VALUES(1,'x');
...
INSERT INTO testrf VALUES(1,'c');
INSERT INTO testrf VALUES(1,'d');
INSERT INTO testrf VALUES(1,'L1');

That is 5 references to ColA = 1

c    C2  d   L1  M1  x   y
1    0   1   1   0   2   0  << adds to 5

There are some recommendations I would add to your query

1. to use QUOTENAME() instead of hard-coding the [ ] brackets.
2. ColB is Varchar so don't use WHEN ColB >=1, use COLB >= '1'

3. COUNT() adds one for every non-null value, so don't use else 0 inside the count

   DECLARE @cols NVARCHAR(MAX), @SQL NVARCHAR(MAX)
   
   SELECT  @cols = STUFF ( (SELECT DISTINCT ',' + QUOTENAME(v.colb)                        
                  FROM testrf AS v                                         
   FOR XML PATH('')), 1, 1, '') 
   
   
   SET @SQL = N'SELECT cola,TotalGroups,AvailableIn,'+ @cols +'
      FROM                     
      (SELECT  v.cola,v.colb,(select count(distinct colb) from testrf) TotalGroups,c.AvailableIn
          FROM testrf AS v  
          inner join
          (select cola,count(case when colb>=''1'' then 1 end) AS AvailableIn FROM testrf
          group by cola) c 
          on c.cola = v.cola                                                                                                  
       ) p                     
          PIVOT                
              (            
                  count(colb)                        
                  FOR colb IN ( '+ @cols + ' )                        
              ) AS pvt'    
   
   EXEC(@SQL) 
   

Answer 2

Rather than counting if colb is greater than 1 (which all your letters are), you want a distinct list (as you have in the parent query) of colb

Here was the only change I made:

(select cola,count(distinct colb) AS AvailableIn FROM testrf

Fiddle