动态数据透视表

Question

我有下表中的详细信息，如下例所示。

例：

表：测试

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

插入：

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'); 

注意：现在我只想显示的记录中， cola属于colc的值A1,A2,A3。

预期结果的：

场景：1

IF @Stuff = A1,A2,A3

然后，结果应该是：

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

备注：在上述结果中，111未出现，因为它也属于A4。

场景：2

IF @Stuff = A1,A2,A8

然后，结果应该是：

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

注：在上面的结果767不是因为出现它属于A3而不是A8。

场景：3

IF @Stuff = A1,A2,A3,A4

然后，结果应该是：

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

注：在上面的结果的准确111属于A1,A2,A3,A4。

方案：4

IF @Stuff = B1

然后，结果应该是：

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

注：在上面的结果中5443时，会出现2次与B1。

尝试：

透视查询：

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

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

PRINT(@Sql) 
EXEC(@Sql)  

获取结果：

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

Answer 1

它是一个不同的问题与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,' [email protected]+ ' 
        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 ('''[email protected]+''') 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 = '[email protected]+' AND b.InList = b.TOTAL 
         )p 
       PIVOT 
       (
        COUNT(colc) 
        FOR colc IN ('[email protected]+') 
       )AS pvt' 
    PRINT(@colcList) 
    PRINT(@Sql) 
    EXEC(@Sql)