如何过滤枢轴结果 [英] How to filter pivot results

问题描述

是否可以从PIVOT结果中排除一些迷信.

Is it possible to exclude some vaules from the PIVOT results.

引用这个问题我想知道是否有可能排除其中的列具有0值的数据透视表.

Referencing this question i would like to know if it is posible to exclude the columns in the Pivot table that has 0 value.

想象一下，EventType会议的计数为0，有可能根本不显示它吗?

Imagine there is a count of 0 for EventType Meeting, is it possible not to show it at all?

推荐答案

我希望您已经从问题中实现了以下解决方案

i hope you have implemented following solution from the question

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(EventType) 
                    from dbo.testTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT year,' + @cols + ' 
            from 
            (
              select EventType, 
                  year = year(date) 
              from dbo.testTable
            ) x
            pivot 
            (
                count(EventType)
                for EventType in (' + @cols + ')
            ) p '

execute(@query)

如果是这样，那么您可以执行以下操作

if so then you can do following

DECLARE @cols AS NVARCHAR(MAX),
@where AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(EventType) 
                    from dbo.testTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @where = ' where ' + STUFF((SELECT distinct ' Or ' + QUOTENAME(EventType) + ' <> 0 '
                    from dbo.testTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,2,3,'')

        set @query = 'SELECT year,' + @cols + ' 
            from 
            (
              select EventType, 
                  year = year(date) 
              from dbo.testTable
            ) x
            pivot 
            (
                count(EventType)
                for EventType in (' + @cols + ')
            ) p ' + @where


execute(@query)

这篇关于如何过滤枢轴结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！