SQL Server 2008 R2:具有where和Have子句的数据透视表的动态查询 [英] SQL Server 2008 R2: Dynamic query for pivot table with where and having clause

问题描述

注意:此信息与以前的信息略有不同.

Note: This post is slightly change with previous post.

我有下表，其详细信息如下例所示.

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('101','1234','A1');
insert into test values('101','4321','A2');
insert into test values('201','5678','A3');
insert into test values('301','8765','A4');
insert into test values('401','9877','A1');
 insert into test values('101','9997','A6');
 insert into test values('201','2277','A1');
 insert into test values('201','1577','A5'); 

注意:现在，我只想显示cola属于colc值的记录.如果用户将colc值作为参数传递给存储过程，则它必须与colc的确切值匹配，属于哪个cola值.

Note: Now I want to show only that records in which cola belongs to colc's values. If the user pass the colc values as a parameter to stored procedure then it has to match the exact value of colc belongs to which cola value.

预期结果:

如果用户通过A1,A2,A6，则结果应为:

If the user pass A1,A2,A6 then the result should be:

cola   A1   A2   A6
--------------------
101    1      1     1

注意:在上述结果中，记录101出现是因为它属于A1,A2,A6而不是其他值. 201不会出现，因为它也属于A1,A3和A5.

Note: In the above result the record 101 appears because it belongs to A1,A2,A6 not other values. 201 not appear because that belongs to A1,A3 and A5 also.

如果用户通过A1，则结果应为:

If the user pass A1 then the result should be:

cola   A1 
--------
401    1  

注意:在上述结果中，记录401出现，因为它属于A1.

Note: In the above result the record 401 appears because it belongs to A1.

数据透视查询:

DECLARE @Stuff varchar(max) = 'A1'
DECLARE @Sql varchar(max)

SET @Sql = 'SELECT cola,' +@Stuff+ '
            from
            (
                select cola,colc
                from test 
                where colc in(''A1'')
                group by cola,colc
                having count(distinct colc) = 1

            )p
            PIVOT
            (
                COUNT(colc)
                FOR colc IN ('+@Stuff+')
            )AS pvt'

PRINT(@Sql)
EXEC(@Sql)      

获得结果:

cola    A1  
----------
101     1
401     1

您可以在这里进行练习:

You can go here for practicle:

http://sqlfiddle.com/#!3/9b1fd/4

推荐答案

您可以使用:

DECLARE @Stuff varchar(max) = 'A1'
        DECLARE @Sql varchar(max)
    DECLARE @totalparam int = 1
        SET @Sql = 'SELECT cola,' +@Stuff+ '
                    from
                    (
                        select cola,colc
                        from test a
                        where colc in(''A1'')
                        group by cola,colc
                        having (select count(colc) from test b where b.cola = a.cola )  = @totalparam

                    )p
                PIVOT
                (
                    COUNT(colc)
                    FOR colc IN ('+@Stuff+')
                )AS pvt'

    PRINT(@Sql)
    EXEC(@Sql)  

这篇关于SQL Server 2008 R2:具有where和Have子句的数据透视表的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！