CSV Values in Dynamic PIVOT's IN clause

Question

I have a dynamic pivot query , which uses csv values in the condition statements .I created a function (splitstring("ABC,sd",",")) which split the csv string and returns in a tabular form, Function is working in the 'IN' clause condtion except inside the 'PIVOT' statement..

insert into @filteredUsers  select * from @users users
 Where (
    (@IncludeInactiveUsers = 1 AND ( users.Status = 'Active' OR (users.Status = 'Inactive')) )
 OR (@IncludeInactiveUsers = 0 AND  users.Status = 'Active')
)


SELECT @columns = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(NAME)
            FROM @filteredUsers
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @SelectedColumns = STUFF((
            SELECT DISTINCT ',ISNULL(' + QUOTENAME(NAME) + ', ''N'') AS [' + NAME + ']'
            FROM @filteredUsers
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');



DECLARE @loanProgramChannels TABLE (
    id UNIQUEIDENTIFIER
    ,NAME VARCHAR(500)
    ,[Broker] VARCHAR(2)
    ,[Correspond] VARCHAR(2)
    ,[Retail] VARCHAR(2)
    );
insert into @loanProgramChannels  select
        id,
        [Loan Program Name],
        ISNULL([Broker],'N') as [Broker],
        ISNULL([Correspond],'N') as [Correspond],
        ISNULL([Retail],'N') as [Retail]
    FROM
        (
            SELECT LoanPrograms.Id
            , LoanPrograms.NAME AS [Loan Program Name]
            , ' Y ' AS Y
            , Channels.NAME AS [ChannelType]
            FROM LoanPrograms
            LEFT JOIN LoanProgramChannels ON LoanPrograms.id = LoanProgramChannels.Loanprogram_Id
            LEFT JOIN Channels ON Channels.id = LoanProgramChannels.Channel_id
        ) programs
        PIVOT
        (
            MAX(Y) FOR [ChannelType] IN ([Broker],[Correspond],[Retail])
        )PivotTable

SELECT Name,Broker,Correspond,Retail,  @SelectedColumns 
FROM
(
    SELECT LoanPrograms.NAME
    ,LP.[Broker]
    ,LP.[Correspond]
    ,LP.[Retail]
    ,(
        CASE 
            WHEN UserParameterValues.value IS NULL
                THEN ' N '
            ELSE ' Y '
            END
        ) AS value
    ,(users.FirstName + space(1) + users.LastName) FullName
    FROM LoanPrograms
    LEFT JOIN UserParameterValues ON LoanPrograms.id = UserParameterValues.ValueId
    LEFT JOIN Parameters on UserParameterValues.ParameterId = Parameters.id 
        AND Parameters.Name = 'UwHierarchy'
    LEFT JOIN users ON UserParameterValues.UserId = Users.Id
    LEFT JOIN @loanProgramChannels LP ON LP.id = loanPrograms.id


) AS UsersTable
PIVOT 
(
SUM(value)
  FOR FullName IN( SELECT *
                FROM dbo.SplitString(@UserNames, ',' ) ) AS pvt

Not sure how can I use the same function inside my PIVOT statements IN clause..?

score 1 · Answer 1 · answered Aug 10 '15 at 05:14

The IN clause in the Pivot clause is not the same as the IN operator in Where clause.
It specifies the columns of the pivot, and SQL Server expects a constant list of columns, and not a table.
In order to make this pivot query to work properly you must use dynamic sql.

I was going to try and re-write your code, but I'm not so sure what columns you need to use in the pivot, so I'll just give you this link that explains dynamic pivot technique in sql server.

CSV Values in Dynamic PIVOT's IN clause

1 Answers1