How to transform a datatable to a ReportingService-like matrix?
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 (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
精彩评论