Pivoting Row Values into Colums

I'd like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to "Pivot" or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don't think it's possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?You can write a view or a stored procedure to produce the result set in your prefered format. The code below will only work when the same month has the same spelling in all rows. E.x., for June, always use either Jun or June. In the following example, I am assuming, Jun, Jul and Aug are used.

Select measure,
MAX(CASE [Month] WHEN 'Jun' THEN Value END) AS Jun,
MAX(CASE [Month] WHEN 'Jul' THEN Value END) AS Jul,
MAX(CASE [Month] WHEN 'Aug' THEN Value END) AS Aug
From YourTable
Group By Measure

Then you can bind the returned result set to your datagrid.

pivoting query on t-sql

gud day.

please help me. im working right now on a case study that will
retrieve/produce a simple report on sql. my problem is I dont know how
to pivot queries like in access. please help me. thanksHere's an example of a simple crosstab in SQL. Monthly Sales by region:

CREATE TABLE DailySales (region CHAR(10), saledate DATETIME, saleamount
DECIMAL(10,2) NOT NULL, PRIMARY KEY (region,saledate))

SELECT region,
SUM(CASE MONTH(saledate) WHEN 1 THEN saleamount END) AS jan,
SUM(CASE MONTH(saledate) WHEN 2 THEN saleamount END) AS feb,
SUM(CASE MONTH(saledate) WHEN 3 THEN saleamount END) AS mar,
SUM(CASE MONTH(saledate) WHEN 4 THEN saleamount END) AS apr,
SUM(CASE MONTH(saledate) WHEN 5 THEN saleamount END) AS may,
SUM(CASE MONTH(saledate) WHEN 6 THEN saleamount END) AS jun,
SUM(CASE MONTH(saledate) WHEN 7 THEN saleamount END) AS jul,
SUM(CASE MONTH(saledate) WHEN 8 THEN saleamount END) AS aug,
SUM(CASE MONTH(saledate) WHEN 9 THEN saleamount END) AS sep,
SUM(CASE MONTH(saledate) WHEN 10 THEN saleamount END) AS oct,
SUM(CASE MONTH(saledate) WHEN 11 THEN saleamount END) AS nov,
SUM(CASE MONTH(saledate) WHEN 12 THEN saleamount END) AS [dec]
FROM DailySales
GROUP BY region

These articles give examples of more complex, dynamic crosstabs:

http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlmag.com/Articles/Inde...ArticleID=15608

--
David Portas
----
Please reply only to the newsgroup
--|||Check out the RAC utility.It is similar to
Access crosstab and has many more features/options.
You will find it as easy to use as Access.

www.rac4sql.net

Pivoting large result sets

Heres the scenario:
We have a database that stores values for different characteristics taken at different times. The data is stored in the following format:

POSTED_UTS CHAR_ID RESULT_VALUE_FLOAT 2005-08-09 14:30:03.907 1859 1.08 2005-08-09 14:30:03.907 1860 1.07 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.937 1859 0.01 2005-08-09 14:30:03.937 1860 0.01 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.953 1756 0.01 2005-08-09 14:30:03.953 1757 0.01 2005-08-09 14:30:03.953 1859 0.01 2005-08-09 14:30:03.953 1859 0.01

The result set for a two hour time span returns >41,000 rows, the result set for a one week time span returns >2.9 million rows. We have multiple data sources, each with its own set of characteristics, each source takes readings at different times and then stores those readings in the above schema. The problem we are running into is, our end users want the data in the following format:

Posted UTS Char 1 Char 2 Char 3 Char 4 . . . Char <n> 2005-08-09 14:00:00.000 2.3 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:30.000 2.3 3.4 5 66.8875 . . . <value n> 2005-08-09 14:00:00.000 NULL 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:00.000 5.6 NULL NULL NULL . . . <value n>

Needless to say, when we pivot on a two hour block, it does take all that long (just over a minute), but when we try to pivot over a one week block, it takes considerably longer, much longer than anyone likes.

Is there a better way of doing this? Would storing the data in the same schema as we want to report in be wiser?

System Information: 2x XEON (Hyperthreaded to 4) 3Ghz
3GB ram
SQL Server 2005
Reporting Services 2005

Thanks for any help.

Wayne E. Pfeffer

The only fast solution I can think of is to use an ssas cube to pivot this

Philippe

pivoting issue

Hi,
I have a expenses table, in this table I have the account, exp_date,
exp_type and amount fields.
I have three exp_type (real, budget and myu) I would like to create a table
with
the fileds account, year_month, real_amount, budget_amount and myu_amount.
I am kind of lost now. How can I do this?
Will it be a query?
do a have a ETL process if yes, how will it work?
Thanks
Search the google archives of microsoft.public.sqlserver.programming group
with the keyword "pivot" and "crosstab" and you will end up with several
solutions posted for similar questions.
Anith

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?
| >
| >
| >
|

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: "examnotes" <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?
| >
| >
| >
|

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...
> >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?
>
>|||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?
| >
| >
| >
|

Pivoting currency text file

Hi!

I have a currency exchange rate flat file with this format:

date;USD;EUR;SEK;

01-01-2004;8.232;8.00;1.43;

02-01-2004;8.232;8.00;1.43;

..and so on.

I need to pivot this to:

01-01-2004;USD;8.232;

01-01-2004;EUR;8.00;

.. and so on..

Anyone got any tips on how to achieve this?

Try the UNPIVOT transformation.

It does exactly what you need to do. There is a useful walkthrough demo in BOL that explains what the UNPIVOT transformation does.

-Jamie

Pivoting a results table MSSQL

I have a query in which I would like to pivot the results

I presently have my results displaying something like this.

OrderNumber Product OrderQuantity
----- ----- -------
0608 Prod1 3
0608 Prod2 12
0608 Prod3 2

What I am after is for the results to display something like this.

OrderNumber Prod1 Prod2 Prod3
----- --- --- ---
0608 3 12 2

This is using SQL Server ver 8.0

SELECT OrderNumber,SUM(CASE WHEN Product='Prod1' THEN OrderQuantity ELSE 0 END) AS Prod1,SUM(CASE WHEN Product='Prod2' THEN OrderQuantity ELSE 0 END) AS Prod2,SUM(CASE WHEN Product='Prod3' THEN OrderQuantity ELSE 0 END) AS Prod3

FROM MyTable

GROUP BY OrderNumber

|||

Thank you, that worked perfectly!

pivoting a recordset

I have a reference table that looks like this

id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl

I want these values to go horizontally into another table matched on id, to look like this:

id | value
========
1,abc def ghi
2, def jkl

I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?never mind, I added an index to the destination table and it finished in 24 seconds :eek:

Pivoting a one-many relationship into 1 row

Hey everyone,
this message comes in two forms, the short version and the long detailed
version-- that way hopefully I can get all the help possible as fast as
possible :)
Short version:
I have a table Names(names_id, name1, name2) that I normalized into 2
tables: Name(name_id, name) and ConnectNames(oldName_id, name_id,
number). The number column just indicates if the name was from the name1
column or the name2 column. Given that I've now normalized this, if I
need to get what was once 1 row (for example: 123, Bob, John), it will
appear as 2 rows if I need to get it by the original nameId as follows:
select oldName_id, name, number
from connectNames cn
inner join name n on n.name_id=cn.oldName_id
where oldName_id=123
would now return:
123 Bob 1
123 John 2
What I need however is for this to be displayed inline like it used to
so that it can be returned in one row (because this gets joined to other
tables). The issue: I'm dealing with millions of rows in this table, and
millions of rows in the other tables that ultimately got joined with the
old denormalized Names table. I've tried using a pivot table approach on
the normalized data to get it in 1 row, but it is very slow when
returning large rowcounts. I've tweaked the indexes, but you can only
get so much performance.
How can I do this better?
Thanks a bunch in advance,
DS
Long detailed version:
I started with a table Names(<pk>names_id, name1, name2). Problem is I
needed to search by name, so I normalized this into a names table and a
cross-reference table:
Name(<pk>name_id, name) and ConnectNames(oldName_id, name_id, number).
I've included the code for this at the bottom of the message for how I
went about this.
Hurra for normalization, now is easy to search for a name:
select oldName_id from connectNames cn inner join name n on n.id=cn.name
where name=@.nameToSearch;
This however presented another problem: I need to be able to display
both name1 and name2 in a single row-- I need to pivot what I just
created (thats actually why I sneaked in the number column into the
cross-ref table to make it easy to pivot). A solution I grabbed from
MSDN was to create a view that I could then join onto twice:
create view connectNamesView
select oldName_id,
MIN(CASE number WHEN 1 THEN name_id END) AS name_id1,
MIN(CASE number WHEN 2 THEN name_id END) AS name_id2,
from connectNames
group by oldName_id
select n1.name, n2.name from connectNamesView cnv
inner join name n1 on n1.name_id=cnv.name_id1
inner join name n2 on n2.name_id=cnv.name_id2
The real issue with this though is that as you may imagine this is
pretty resource intensive, especially when you have several million
records in these tables, and when you join oldName_id to another table
with several million records like this:
select id, product, name1, name2
from Owners o -- note: owners has millions of rows too
inner join connectNamesView cnv on cnv.name_id=o.name_id
inner join name n1 on n1.name_id=cnv.name_id1
inner join name n2 on n2.name_id=cnv.name_id2
where id between 10000 and 20000
Running this takes ages when its joined to another table. Is there a
better way to improve performance or to denormalize the results JUST for
display (display them in 2 columns)? (by the way, is denormalize the
correct term for doing what I need to here).
Thanks in advance for the help and reading this long-winded post :)
-DS
To normalise the table Id did the following:
create table tmpName (id int, name varchar(20), number tinyint);
insert into tmpName (id, name, number) select name_id, name1, 1
insert into tmpName (id, name, number) select name_id, name2, 2
create table Name (id int not null identity(1,1), name varchar(20))
insert into Name (name) select distinct name from tmpName
-- at this point the Names table has been normalized; now to create the
one to many relationship:
create table ConnectNames(oldName_id int, name_id int, number tinyint)
insert into ConnectNames(oldName_id, name_id, number)
select t.id, n.id, t.number
from name n
inner join tmpName t on t.name = n.name
drop table tmpNameProviding the relationship is always 1 to 1 (exactly one name for each
old_name), then this might work (untested):
select name as Name1
,Name 2
=(select name
from Names OldNames
where (OdlNames.oldName_id = Names.name_id))
from Names
For a better solution post DDL, sample data, preferably with expected result
s.
ML

PIVOTing

Hi there!
I've played a little bit with the PIVOT operator in SQL Server 2005 and have
a question:
Can I use a Sub-Select for the values in the IN clause? Because I can't
hardcode these values because they are dynamic (based on user input)...
Thanks
Klaus Aschenbrenner
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerKlaus
DECLARE @.st VARCHAR(50)
SET @.st='5,6'
EXEC('
SELECT *
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN('+@.st+')')
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:ea4lgqnRGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Hi there!
> I've played a little bit with the PIVOT operator in SQL Server 2005 and
> have a question:
> Can I use a Sub-Select for the values in the IN clause? Because I can't
> hardcode these values because they are dynamic (based on user input)...
> Thanks
> Klaus Aschenbrenner
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||> Can I use a Sub-Select for the values in the IN clause? Because I
> can't hardcode these values because they are dynamic (based on user
> input)...
Not in SQL Server 2005. Maybe next version...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/