动态数据透视表 [英] Dynamic pivot table
问题描述
我有下表，其详细信息如下例所示.
I have the following table with the details as shown below in the example.
示例:
表:测试
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的记录.
Note: Now I want to show only that records in which cola belongs to colc's values A1,A2,A3.
预期结果:
场景:1
IF @Stuff = A1,A2,A3
IF @Stuff = A1,A2,A3
那么结果应该是:
cola A1 A2 A3
------------------
767 1 1 1
注意:在上述结果中，111未出现，因为它也属于A4.
Note: In the above result the 111 not appeared because of it also belongs to A4.
场景:2
IF @Stuff = A1,A2,A8
IF @Stuff = A1,A2,A8
那么结果应该是:
cola A1 A2 A8
------------------
8998 1 1 1
注意:在上述结果中，767未出现，因为它属于A3而不是A8.
Note: In the above result the 767 not appeared because of it belongs to A3 not A8.
场景:3
IF @Stuff = A1,A2,A3,A4
IF @Stuff = A1,A2,A3,A4
那么结果应该是:
cola A1 A2 A3 A4
---------------------
111 1 1 1 1
注意:在以上结果中，111完全属于A1,A2,A3,A4.
Note: In the above result the 111 exactly belongs to A1,A2,A3,A4.
场景:4
IF @Stuff = B1
IF @Stuff = B1
那么结果应该是:
cola B1
----------
5443 2
注意:在以上结果中，5443与B1一起出现了两次.
Note: In the above result the 5443 is appeared two times with the B1.
数据透视查询:
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)
获得结果:
cola A1 A2 A3
-------------------
111 1 1 1
5443 0 0 0
767 1 1 1
8998 1 1 0
