Dynamic Pivot table query with condition

Question

I have the following table with the details as shown below in the example.

Example:

Table: test

create table test
(
 cola varchar(10),
 colb varchar(10),
 colc varchar(10)
);

Insertion:

insert into test values('111','222','A1');
insert into test values('111','333','A2');
insert into test values('111','344','A3');
insert into test values('111','444','A4');
insert into test values('767','222','A1');
insert into test values('767','333','A2');
insert into test values('767','344','A3');
insert into test values('5443','555','B1');
insert into tft values('8998','222','A1');
insert into tft values('8998','333','A2');    

Note: Now I want to show only that records in which cola belongs to colc's values A1,A2,A3.

Expected Result:

cola   A1  A2  A3
------------------
111    1   1   1
767    1   1   1

Attempt:

Pivot Query:

DECLARE @Stuff varchar(max) = 'A1,A2,A3'
DECLARE @Sql varchar(max)

SET @Sql = 'SELECT cola,' +@Stuff+ '
            from
            (
                select cola,colc
                from test 
            )p
            PIVOT
            (
                COUNT(colc)
                FOR colc IN ('+@Stuff+')
            )AS pvt'

PRINT(@Sql)
EXEC(@Sql)      

Getting Result:

cola    A1  A2  A3
-------------------
111     1   1   1
5443    0   0   0
767     1   1   1
8998    1   1   0

Answer 1

The key to getting the result will be to first return the cola values that have A1, A2, and A3 in colc. Using IN is basically saying A1 or A2 or A3 so you will return rows that have one of these, not all of them.

In order you get the cola rows that have all of them, you will need to use a GROUP BY and a HAVING clause to go along with your WHERE clause filter. The basic query would be:

select cola
from test
where colc in ('A1', 'A2', 'A3')
group by cola
having count(distinct colc) = 3;

See SQL Fiddle with Demo. Breaking the query down, you'll see there is a WHERE clause to filter your colc values. You will use GROUP BY on cola along with a HAVING clause. The HAVING is used to the get the distinct number of colc values. In this case, the HAVING clause will be checking for rows that have a total of 3 colc values when filtered.

Once you have this list, then you can get the final result. Instead of using PIVOT you can use an aggregate function with a CASE expression.

select 
  t.cola, 
  sum(case when t.colc = 'A1' then 1 else 0 end) A1,
  sum(case when t.colc = 'A2' then 1 else 0 end) A2,
  sum(case when t.colc = 'A3' then 1 else 0 end) A3
from test t
inner join
(
  select cola
  from test
  where colc in ('A1', 'A2', 'A3')
  group by cola
  having count(distinct colc) = 3
) d
  on t.cola = d.cola
group by t.cola;

See SQL Fiddle with Demo

Typically you'd use dynamic SQL for data that you have unknown values on but if you want to use it for this, you can use the following query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = 'A1, A2, A3'

set @query = N'SELECT cola, ' + @cols + N' 
            from 
            (
              select t.cola, t.colc
              from test t
              inner join
              (
                select cola
                from test
                where colc in (''A1'', ''A2'', ''A3'')
                group by cola
                having count(distinct colc) = 3
              ) d
                on t.cola = d.cola
            ) x
            pivot 
            (
                count(colc)
                for colc in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo