推荐订阅源

ALTER PROCEDURE [dbo].[usp_Tender_LatestPriceList] @projectid VARCHAR(50)
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
        SELECT  @columns=STRING_AGG(dbo.f_GetProcessRoundTitle(RoundNumber), ', ')
            FROM(SELECT     DISTINCT RoundNumber
                     FROM   dbo.Tender_RoundInfo
                     WHERE  ProjectID=@projectid) AS R;

        SELECT      a.SupplierName, dbo.f_GetProcessRoundTitle(a.RoundNumber) AS RoundName, a.Price
            INTO    #x
            FROM    dbo.Tender_LatestPrice   a
                    INNER JOIN dbo.Tender_RoundInfo b ON a.ProjectID=b.ProjectID AND   a.RoundNumber=b.RoundNumber
            WHERE   a.ProjectID=@projectid;
        -- 构造动态 PIVOT 查询
        SET @sql=N'
                    SELECT *
                    FROM #x AS src
                    PIVOT (
                        MAX(Price)
                        FOR RoundName IN ('+@columns+N')
                    ) AS pvt;
                    '       ;
        EXEC dbo.sp_executesql @sql;
    END;