带条件的动态数据透视表查询
我有下面的表格，详细信息如下例所示。
示例 ：
表：测试
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');
注意 ：现在我只想显示cola属于colc值A1,A2,A3 。
预期成果 ：
cola A1 A2 A3
------------------
111 1 1 1
767 1 1 1
尝试：
数据透视查询 ：
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
获得结果的关键是首先返回colc A1 ， A2和A3的cola值。 使用IN基本上是说A1或A2或A3所以你将返回具有其中之一的行，而不是全部。
为了获得包含所有这些cola行，您需要使用GROUP BY和HAVING子句以及您的WHERE子句过滤器。 基本查询将是：
select cola
from test
where colc in ('A1', 'A2', 'A3')
group by cola
having count(distinct colc) = 3;
使用Demo查看SQL小提琴。 打破查询，你会看到有一个WHERE子句来过滤你的colc值。 您将使用GROUP BY cola以及HAVING子句。 HAVING用于获取不同数量的colc值。 在这种情况下， HAVING子句将检查过滤时总共有3个colc值的行。
一旦你有这个清单，那么你可以得到最终结果。 除了使用PIVOT您可以使用具有CASE表达式的聚合函数。
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;
使用Demo查看SQL小提琴
通常情况下，您将动态SQL用于您有未知值的数据，但如果您想为此使用它，则可以使用以下查询：
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;
使用Demo查看SQL小提琴
你还必须过滤源数据：
DECLARE @Stuff varchar(max) = 'A1,A2,A3'
DECLARE @StuffForFilter varchar(max) = '''A1'',''A2'',''A3'''
DECLARE @Sql varchar(max)
SET @Sql = 'SELECT cola,' +@Stuff+ '
from
(
select cola,colc
from test
where colc IN ('+@StuffForFilter+')
)p
PIVOT
(
COUNT(colc)
FOR colc IN ('+@Stuff+')
)AS pvt'
PRINT(@Sql)
EXEC(@Sql)
尝试这个。 要筛选colc中包含A1 ， A2和A3的Cola值， colc使用conditional aggregate 。
DECLARE @Stuff varchar(max) = 'A1,A2,A3'
DECLARE @StuffForFilter varchar(max) = '''A1'',''A2'',''A3'''
DECLARE @Sql Nvarchar(max)
SET @Sql = 'SELECT cola,' +@Stuff+ '
from
(
select t.cola,t.colc
from #test t join (SELECT cola from (SELECT Max(CASE WHEN colc = ''A1'' THEN 1 END) [a1],
Max(CASE WHEN colc = ''A2'' THEN 2 END) [a2],
Max(CASE WHEN colc = ''A3'' THEN 3 END) [a3] ,
cola
FROM #test
WHERE colc IN( '+@StuffForFilter+' )
GROUP BY cola) A
WHERE [a1] IS NOT NULL
AND [a2] IS NOT NULL
AND [a3] IS NOT NULL ) B on t.cola=B.cola
)p
PIVOT
(
COUNT(colc)
FOR colc IN ('+@Stuff+')
)AS pvt'
EXEC sp_executesql @Sql
输出：
+-----+----+---+----+
|cola | A1 | A2| A3 |
+-----+----+---+----+
|111 | 1 | 1 | 1 |
|767 | 1 | 1 | 1 |
+-----+----+---+----+