Pivoting DataSet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have from SQL a stored procedure that is vaguely doing something
like:
SELECT
FormName,
frmDueDate,
DepartmentName,
Country
frmStatus
FROM
wholeBunchOfTables

So data will be returned like:
Form1 ITDept USA Incomplete
Form2 ITDept UK Completed
Form1 HRDept FR Completed
Form2 HRDept FR Missing

The DataSet resultant from the Stored procedure execution is binded to a
sortable DataGrid. However, I would like the data "pivoted":
i.e.
Form1 Form2
ITDept USA Incomplete Completed
HRDept FR Completed Missing

Is this at all possible (natively)??
- Can't think of any pivoting SQL operators??
- Can't pivot on ASP.NET/ADO.NET?? Would I have to resort to
--- creating another DataSet with columns pivoted?? or
--- render my own table, etc. and handle by own sorting?
 
Reporting Services does this quite easily... are you creating a report?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
This article is *Excellent*!! Just about what I wanted!

However, how can I modify the following:
<asp:datagrid id="OutstandingFormsDataGrid" runat="server"
AutoGenerateColumns="False" ShowHeader="true"
EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
OnSortCommand="SortCurrentMonth_OnClick"
HeaderStyle-Height="25px">
<ItemStyle CssClass="data"></ItemStyle>
<HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
DataTextField="??These are pivoted columns??" SortExpression="???"
HeaderText="Form Name"></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Due Date"
</Columns>
</asp:datagrid>

The following are being displayed at the moment with
"AutoGenerateColumns=True":
frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete

Ideally I want:
Country Form1 Form2 Form3 Form 4
UK complete complete missing complete
US missing missing complete complete

(with the frmInstId) embedded as hyperlink the the form status

Is it possible?
 
Patrick,

As far as I know, currently we have no better ways to convert the result
table to your ideal table than doing statistics manually. We can write a
function to check how many distinct values are there in the Country column.
Then we select rows for each Country value and generate one row with Form
values from each row.

To set a hyperlink column, you can set a hyperlink column or buttong column
like the following:

<asp:DataGrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 8px; POSITION:
absolute; TOP: 8px" runat="server">
<Columns>
<asp:HyperLinkColumn></asp:HyperLinkColumn>
<asp:ButtonColumn Text="Select" CommandName="Select"></asp:ButtonColumn>
</Columns>
</asp:DataGrid>

Or you can achieve this in the property builder, and bind the text to
column values. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Wow, thanks Kevin for your help.

However, I do not think the Hyperlink column can be as simple as you
suggested, as I do not know at *design time* what the column names will be,
as I am pivotting:

frmInstID Country Form Status
1 UK Form1 Complete
2 UK Form2 Complete
3 UK Form3 Missing
4 UK Form4 Complete
5 US Form1 missing
6 US Form2 missing
7 US Form3 Complete
8 US Form4 Complete

into

frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete
 
Thanks for Cor's sample code.

Hi Patrick,

The code in my last post is to add the column at design time. However, you
can also add the columns at runtime. We can just add columns to the
DataGrid.Columns collection using code. Please check the following link for
more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemwebuiwebcontrolsdatagridclasscolumnstopic.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top