返回表的 UDF 中的 SQL Server Dynamic Pivot [英] SQL Server Dynamic Pivot in UDF that returns table

问题描述

我有以下查询，它使用动态数据透视以我期望的格式返回一些数据:

I have the following query that uses dynamic pivot to return some data in the format I am expecting:

CREATE TABLE TEMPDOCS (DOCID INT, NAME VARCHAR(30))
CREATE TABLE PROVIDERIDS (ID INT, PARENTID INT, QUALIFIER VARCHAR(20), IDENTIFIER VARCHAR(30))
INSERT INTO TEMPDOCS VALUES (1, 'ROGER, HARPER')
INSERT INTO TEMPDOCS VALUES (2, 'WALTZ, HEALY')

INSERT INTO PROVIDERIDS VALUES (1, 1, 'DEA', 'D12345')
INSERT INTO PROVIDERIDS VALUES (2, 1, 'NPI', 'N12345')
INSERT INTO PROVIDERIDS VALUES (3, 1, 'LIC', 'L12345')
INSERT INTO PROVIDERIDS VALUES (4, 2, 'NPI', 'N23456')
INSERT INTO PROVIDERIDS VALUES (5, 2, 'REG', 'R23456')

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @id as int = 1;

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QUALIFIER) 
            FROM PROVIDERIDS
            LEFT OUTER JOIN TEMPDOCS on TEMPDOCS.DOCID = PROVIDERIDS.PARENTID
            WHERE DOCID = @id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DOCID, NAME, ' + @cols + ' from 
            (
                select TEMPDOCS.DOCID, TEMPDOCS.NAME, PROVIDERIDS.QUALIFIER, PROVIDERIDS.IDENTIFIER FROM TEMPDOCS
                LEFT OUTER JOIN PROVIDERIDS ON PROVIDERIDS.PARENTID=TEMPDOCS.DOCID
                WHERE TEMPDOCS.DOCID = ' + CAST(@ID AS VARCHAR(30)) + '
           ) x
            pivot 
            (
                MAX(IDENTIFIER)
                FOR QUALIFIER IN (' + @cols + ')
            ) p '


execute(@query)

我想将此代码转换为返回表的用户定义函数.我尝试这样做，但无法使其正常工作:

I want to convert this code into an user defined function that return a TABLE. I tried doing it but not able to make it work:

CREATE FUNCTION udfGetProviderIds (@DoctorId INT)
RETURNS TABLE
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @id as int = 1;

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QUALIFIER) 
            FROM PROVIDERIDS
            LEFT OUTER JOIN TEMPDOCS on TEMPDOCS.DOCID = PROVIDERIDS.PARENTID
            WHERE DOCID = @id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DOCID, NAME, ' + @cols + ' from 
            (
                select TEMPDOCS.DOCID, TEMPDOCS.NAME, PROVIDERIDS.QUALIFIER, PROVIDERIDS.IDENTIFIER FROM TEMPDOCS
                LEFT OUTER JOIN PROVIDERIDS ON PROVIDERIDS.PARENTID=TEMPDOCS.DOCID
                WHERE TEMPDOCS.DOCID = ' + CAST(@ID AS VARCHAR(30)) + '
           ) x
            pivot 
            (
                MAX(IDENTIFIER)
                FOR QUALIFIER IN (' + @cols + ')
            ) p '


    RETURN execute(@query)  
END

我收到以下错误:

Msg 102, Level 15, State 31, Procedure udfGetProviderIds, Line 32
Incorrect syntax near 'BEGIN'.

有没有办法在不声明 TABLE 和返回子句中的结构的情况下完成这项工作?或者，是否有更好的方法使用动态 Pivot 创建 UDF?

Is there a way to make this work without declaring the TABLE and the structure in the returns clause? Or, is there a better way of creating UDF with dynamic Pivot?

推荐答案

就像 Aaron 解释的那样，您不能在 UDF 中使用动态 sql 但我可以想出一种方法，只需将您的所有函数定义都放在一个存储过程中，您就可以将存储过程返回的结果放入一个临时表中，就像这样

Like Aaron has explained you cannot use dynamic sql in UDF but I can think of a way around just put all of your functions definition in a store procedure and you can get the results returned from stored proc into a temp table something like this

CREATE PROC usp_GetProviderIds 
@DoctorId INT
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @id as int = 1;

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QUALIFIER) 
            FROM PROVIDERIDS
            LEFT OUTER JOIN TEMPDOCS on TEMPDOCS.DOCID = PROVIDERIDS.PARENTID
            WHERE DOCID = @id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DOCID, NAME, ' + @cols + ' from 
            (
                select TEMPDOCS.DOCID, TEMPDOCS.NAME, PROVIDERIDS.QUALIFIER, PROVIDERIDS.IDENTIFIER FROM TEMPDOCS
                LEFT OUTER JOIN PROVIDERIDS ON PROVIDERIDS.PARENTID=TEMPDOCS.DOCID
                WHERE TEMPDOCS.DOCID = ' + CAST(@ID AS VARCHAR(30)) + '
           ) x
            pivot 
            (
                MAX(IDENTIFIER)
                FOR QUALIFIER IN (' + @cols + ')
            ) p '


    EXECUTE sp_Executesql @query  
END

然后您可以通过执行以下操作将返回的结果集放入临时表

And then you can get the returned result set into a temp table by doing the following

CREATE TABLE #TempTable (Col1, Col2.....)
INSERT INTO #TempTable
EXEC usp_GetProviderIds @DoctorId

这篇关于返回表的 UDF 中的 SQL Server Dynamic Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！