Ted Odukalets Ted Odukalets - 2 days ago 4
SQL Question

Is there a way to filter rows in a dynamic pivot stored procedure?

I can filter columns but not rows.

Here is my iGuide Table data I use with my PIVOT:




/////////////////////////////////////////////////////

Zone Enabled Channel DMA Region HQ Machine
ACC 1 AEN Norfolk Other virginia dzsfk
ACC 1 CNN Norfolk Other virginia dzsfk
ACC 1 ESPN Norfolk Other virginia dzsfk
ACC 1 HIST Norfolk Other virginia dzsfk
ACC 1 FOOD Norfolk Other virginia dzsfk
ANJ 1 AEN Paducah Pacific hqs3ftbgwi adanj
ANJ 1 ESPN Paducah Pacific hqs3ftbgwi adanj
ANJ 1 HIST Paducah Pacific hqs3ftbgwi adanj
ANJ 1 CNN Paducah Pacific hqs3ftbgwi adanj
CHD 1 ESPN Denver Pacific hqs3ftbgwi adchd
CHD 1 FOOD Denver Pacific hqs3ftbgwi adchd
CHD 1 DISC Denver Pacific hqs3ftbgwi adchd
CHI 1 AEN Denver Pacific hqs3ftbgwi adchd
CHI 1 FOOD Chico Pacific hqs4ftbgwi adv1chicca
CHI 1 ESPN Chico Pacific hqs4ftbgwi adv1chicca
CHI 1 CNN Chico Pacific hqs4ftbgwi adv1chicca

/////////////////////////////////////////////////////

Here is an example of my initial result of Stored Procedure without any filters:





CHANNEL | ACC | ANJ | CHD | CHI |
AEN 1 1 NULL 1
CNN 1 1 NULL 1
ESPN 1 1 1 1
FOOD 1 NULL 1 1
HIST 1 1 NULL NULL
DISC NULL NULL 1 NULL
_________________________________________________


HERE is my current result I get after applying a zone @Filter = 'ACC':

_________________________________________________
CHANNEL | ACC |
AEN 1
CNN 1
ESPN 1
FOOD 1
HIST 1
DISC NULL
_________________________________________________


My desired result is to get this:

_________________________________________________
CHANNEL | ACC |
AEN 1
CNN 1
ESPN 1
FOOD 1
HIST 1
_________________________________________________


How do I get rid of empty NULL rows when applying my filter?

HERE IS MY CODE:

USE [Media_Ops]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Usp_GetWHERE]
@Filter Varchar(MAX)

AS

DECLARE @columns NVARCHAR(MAX)
,@sql NVARCHAR(MAX)

SET @columns = N''
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(Zone)
FROM (select distinct Zone
from iGuide WHERE (DMA LIKE @Filter) OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (Company LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
) AS T
--select @columns
SET @sql = N'
SELECT Distinct Channel, ' + STUFF(@columns, 1, 2, '') + '
FROM iGuide
PIVOT
(
count(Zone) FOR Zone IN ('
+ STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
+ ')
) AS Pivot1 ORDER BY Channel
'
PRINT @sql;
EXEC sp_executesql @sql;

Answer

SQL DEMO Create a new variable @zone to filter before pivot

CREATE Procedure [dbo].[Usp_GetWHERE]
    @Filter Varchar(MAX)

AS

   DECLARE @columns NVARCHAR(MAX)      
           ,@sql NVARCHAR(MAX)
           ,@zone NVARCHAR(MAX)

    --Get column names for entire pivoting
    SET @columns = N''
    SELECT @columns += N', ' + QUOTENAME(Zone)
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    --Get column names for filter source    
    SET @zone = N''
    SELECT @zone += N', ' + QUOTENAME(Zone, '''')
      FROM (select distinct Zone 
            from iGuide  WHERE (DMA LIKE @Filter)   OR (Channel = @Filter) OR (@Filter = ' Select All') OR (@Filter = 'Select All') OR (Zone LIKE @Filter) OR (Machine LIKE @Filter) OR (HQ LIKE @Filter) OR (Region LIKE @Filter)
         ) AS T

    SET @zone = '(' + substring(@zone, 3, len(@zone)-2) + ')';         

    SET @sql = N'
    SELECT  [Channel], ' + STUFF(@columns, 1, 2, '') + '
    FROM (SELECT Channel, [Zone]
          FROM iGuide 
          WHERE [Zone] IN ' + @zone + ')  as source
    PIVOT
    (
      count([Zone]) FOR [Zone] IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS Pivot1  ORDER BY Channel
    ' 
    select @sql;
    EXEC sp_executesql @sql;

GO 
EXECUTE [dbo].Usp_GetWHERE N'ACC';              

OUTPUT

enter image description here

Comments