How to filter pivot results

问题:

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

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

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

回答1:

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)