Pivoting DataSet

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.
__________________________________
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>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?
|||Patrick
This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
Adrian Moore
http://www.queryadataset.com
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>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?
|||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?
"Adrian Moore" wrote:

> Patrick
> This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
> Adrian Moore
> http://www.queryadataset.com
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>
>
|||Hello Patrick,
This seems normal behavior if you use the code from the following link
directly:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
The code adds a new row in the datatable of dataset from the original
table. You need to modify the code so that it can search all the existing
rows in datatable for the row with the same key coulumn such as Country in
your table. If there is a row existing, you shall create a new column to
this row other than create a new row.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Pivoting DataSet
| thread-index: AcV415h2UiiUvr4YRaGnKv5nBmTZ3Q==
| X-WBNR-Posting-Host: 198.240.130.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com>
<OsVZAg4dFHA.2288@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Pivoting DataSet
| Date: Fri, 24 Jun 2005 09:13:02 -0700
| Lines: 85
| Message-ID: <1A21D301-964E-4E83-9397-2F6B50C6403D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft .public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:61330
microsoft.public.dotnet.framework.adonet:31648
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| 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?
|
| "Adrian Moore" wrote:
|
| > Patrick
| >
| > This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
| >
| > Adrian Moore
| > http://www.queryadataset.com
| >
| >
| > "Patrick" <questions@.newsgroup.nospam> wrote in message
| > news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
| > >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?
| >
| >
| >
|