sql-server - 当我们使用 group by 时，视图中的行数据值可以显示在单独的列中

Question

我的 SQL 代码是这样的：

CREATE VIEW [dbo].[UDV_Project_Summary_With_Beneficiary]
    SELECT 
        FiscalYear.FiscalYearName AS [Fiscal Year],
        dbo.Region.RegionName AS Region,
        COUNT(DISTINCT(Project.ProjectID)) AS [No of Projects],
        SUM(ISNULL(Project.Amount, 0) + ISNULL(Project.ContAmount, 0)) AS Amount,
        [dbo].[UDF_Get_BName](1, [dbo].[ProjectBeneficiaryDetail].BeneficiaryTypeID) AS Beneficiaries,
        SUM(ISNULL(ActualNoOfMale, 0)) AS Men,
        SUM(ISNULL(ActualNoOfFemale, 0)) AS Women,
        SUM(ISNULL(ActualNoOfMale, 0) + ISNULL(ActualNoOfFemale, 0)) AS Total
    FROM             
        dbo.Project 
    INNER JOIN
        dbo.Commune ON dbo.Project.CommuneIdProject = dbo.Commune.CommuneId 
    INNER JOIN
        dbo.Arrondissement ON dbo.Arrondissement.[ArrondissementId] = dbo.Commune.[ArrondissementId] 
    INNER JOIN
        dbo.Cercle ON dbo.Cercle.CercleId = dbo.Arrondissement.CercleId 
    INNER JOIN
        dbo.Region ON dbo.Region.RegionId = dbo.Cercle.RegionId 
    INNER JOIN
        ImplementingPartner ON dbo.ImplementingPartner.IPID = dbo.Project.IPID  
    INNER JOIN
        FiscalYear ON dbo.Project.FiscalYearID = dbo.FiscalYear.FiscalYearID 
    INNER JOIN
        ProjectCategory ON dbo.Project.ProjectCategoryID = dbo.ProjectCategory.ProjectCategoryID 
    INNER JOIN
        ProjectStatus ON dbo.Project.ProjectStatusID = dbo.ProjectStatus.ProjectStatusID 
    INNER JOIN
        [dbo].[ImplementingPartnerType] ON [dbo].[ImplementingPartnerType].[ImplementingPartnerTypeID] = ImplementingPartner.[ImplementingPartnerTypeID] 
    LEFT JOIN
        [dbo].[ProjectBeneficiaryDetail] ON dbo.Project.ProjectID =  dbo.[ProjectBeneficiaryDetail].ProjectID 
    GROUP BY  
        FiscalYear.FiscalYearName, Region.RegionName, [dbo].[ProjectBeneficiaryDetail].BeneficiaryTypeID

结果显示在此屏幕截图中：

但我想使用 SQL 代码显示如下图像数据。请帮我怎么做

我试图通过使用 PIVOT 来做到这一点，但没有成功。我尝试像第 1 步一样，我从视图中插入了 Beneficiary_ProjectType_Template 中的值

从 [dbo] 中选择 [干预区域]、[项目数量]、金额、受益人、男性、女性、总计到 Beneficiary_ProjectType_Template。[UDV_Project_Summary_With_Beneficiary_ProjectType]

步骤 2 在 SQL 下面

声明 @cols 为 NVARCHAR(MAX)，@query 为 NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(dbo.Beneficiary_ProjectType_Template.[No of Projects]) from Beneficiary_ProjectType_Template group by Beneficiary_ProjectType_Template.[No of Projects] order by Beneficiary_ProjectType_Template.[No of Projects] FOR XML PATH('' ), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT [Intervention Areas] ' + @cols + ' from ( select [Intervention Areas], [No of Projects], Amount, Beneficiaries from Beneficiary_ProjectType_Template ) x pivot ( count(Amount) for Beneficiaries in (' + @列 + ') ) p '

执行（@查询）；帮助我如何使用正确的方式来获得如下图所示的结果。提前致谢

标签： sql-server