开发者

How to transform a datatable to a ReportingService-like matrix?

Question:

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

How to transform a datatable to a ReportingService-like matrix?

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

How to transform a datatable to a ReportingService-like matrix?

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 (R开发者_开发问答M_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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜