Dynamic pivot table

问题:

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');    
insert into tft values('8998','353','A8'); 
insert into test values('5443','555','B1');

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

Expected Result's:

Scenario:1

IF @Stuff = A1,A2,A3

Then the result should be:

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

Note: In the above result the 111 not appeared because of it also belongs to A4.

Scenario:2

IF @Stuff = A1,A2,A8

Then the result should be:

cola   A1  A2  A8
------------------
8998    1   1   1

Note: In the above result the 767 not appeared because of it belongs to A3 not A8.

Scenario:3

IF @Stuff = A1,A2,A3,A4

Then the result should be:

cola   A1  A2  A3  A4
---------------------
111    1   1   1   1

Note: In the above result the 111 exactly belongs to A1,A2,A3,A4.

Scenario:4

IF @Stuff = B1

Then the result should be:

cola   B1  
----------
5443   2   

Note: In the above result the 5443 is appeared two times with the B1.

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

回答1:

Is it a different issue with this?

DECLARE @Stuff varchar(max) = 'A1,A2,A3'
        DECLARE @Sql varchar(max)
DECLARE @totalparam varchar(max) = len(@Stuff) - len(replace(@Stuff,',',''))+1
DECLARE @colcList varchar(max) = REPLACE(@Stuff,',',''',''')
        SET @Sql = 'SELECT cola,' +@Stuff+ '
                    FROM
                    (SELECT a.cola, a.colc from test a JOIN (
                        SELECT cola, COUNT(InList) AS InList, COUNT(TOTAL) AS TOTAL FROM (
                               SELECT cola, 
                                     CASE WHEN colc IN ( '''+@colcList+''') THEN
                                         1
                                      END AS InList, 
                                      1 AS TOTAL FROM test
                                group by cola, colc) AS tester group by cola) b  ON a.cola = b.cola
                        WHERE b.TOTAL = '+@totalparam+' AND b.InList = b.TOTAL
                        )p
                PIVOT
                (
                    COUNT(colc)
                    FOR colc IN ('+@Stuff+')
                )AS pvt'
    PRINT(@colcList)
    PRINT(@Sql)
    EXEC(@Sql)