如何将数据类型转换为类似ReportingService的矩阵？ [英] How to transform a datatable to a ReportingService-like matrix?

问题描述

问题：

我有从数据库检索的数据，如下所示：

现在我需要将其转换为以下格式，以便能够绘制一个饼图。





在ReportingService，有Matrix控件来实现这一点，但是我可以使用在普通C＃中实现相同，以便将其呈现为PieChart图像？

请注意，建筑物的数量以及使用类型是可变的，并且提前未知。

编辑


感谢Magnus和Google：

  SELECT * FROM 
 （
 SELECT 
 STE_Designation AS RPT_Site 
，BDG_Designation AS RPT_Building 
，UG_Code AS RPT_Usage_Code 
，UG_Caption AS RPT_Usage 
，SUM（MP_RMArea_Area）AS RPT_Area 
 
 FROM V_RPT_RoomDetail 
 
 WHERE（RM_MDT_ID = 1）
 
 GROUP BY 
 STE_Designation 
，BDG_Designation 
，UG_Code 
，UG_Caption 
 
 --ORDER BY STE_Designation，BDG_Designation，UG_Code，UG_Caption 
）AS SourceTable 
 PIVOT 
（
 SUM（RPT_Area）
 FOR RPT_Building IN（[Building1]，[Building2]，[BuildingN]）
）AS PivotTable 
 
 ORDER BY RPT_Site，RPT_Usage_Code 
  
 
 
 
枢轴列需要通过select distinct生成。
解决方案
您应该看看SQL数据透视运算符
  http://msdn.microsoft.com/en-us/library/ms177410.aspx  
 
Question:

I have data that I retrieve from a database, which looks like this:






Now I need to transform it to the below format, in order to be able to draw a piechart.





In ReportingService, there is the Matrix control to achieve this, but what can I use to achieve the same in ordinary C#, in order to render it to a PieChart image ?

Note that the number of buildings as well as the usage-types is variable and not known ahead of time.

Edit:


Solved thanks to Magnus and Google:
SELECT * FROM
(
    SELECT 
         STE_Designation AS RPT_Site 
        ,BDG_Designation AS RPT_Building 
        ,UG_Code AS RPT_Usage_Code 
        ,UG_Caption AS RPT_Usage 
        ,SUM(MP_RMArea_Area) AS RPT_Area 

    FROM V_RPT_RoomDetail 

    WHERE (RM_MDT_ID = 1) 

    GROUP BY 
         STE_Designation
        ,BDG_Designation
        ,UG_Code
        ,UG_Caption 

    --ORDER BY STE_Designation, BDG_Designation, UG_Code, UG_Caption 
) AS SourceTable
PIVOT
(
    SUM(RPT_Area)
    FOR RPT_Building IN ([Building1], [Building2], [BuildingN])
) AS PivotTable

ORDER BY RPT_Site, RPT_Usage_Code
Where the pivot columns need to be generated in code by a select distinct.
解决方案
You should have a look at the SQL Pivot operator
http://msdn.microsoft.com/en-us/library/ms177410.aspx

                        
这篇关于如何将数据类型转换为类似ReportingService的矩阵？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！