动态透视的 IN 子句中的 CSV 值

Question

我有一个动态枢轴查询，它在条件语句中使用csv值。我创建了一个函数(splitstring("ABC,sd"，"，"))，它分割csv字符串并以表格形式返回，函数在' in '子句条件下工作，除了在' pivot '语句内。

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 

不确定我如何在我的PIVOT语句内使用相同的函数IN子句…?

Answer 1

Pivot子句中的IN子句与Where子句中的IN运算符不相同。
它指定了数据透视的列，SQL Server期望的是列的常量列表，而不是表。
为了使这个数据透视查询正常工作，您必须使用动态sql。

我打算尝试重写你的代码，但我不确定你需要在pivot中使用哪些列，所以我只给你这个解释sql server中动态pivot技术的链接。