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 co…

Accepted Answer

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

Dynamic Pivot table query with condition

Solution

Problem