Need help writing query/ stored proc

I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
__________________________________________________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4 ActualTotal EstimatedTotal
> > 100 500 200 150 950 1400
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a pivot table
> type query, which can be done with a "monster" CASE statement in SQL 2000 or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to work
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
> >I need to write query or stored proc that is going to be used to gen. a
> > report
> > This is a structure of my table
> >
> > Id Date ItemType OrdersPlaced
> > 1 08/21 1 100
> > 1 08/21 2 500
> > 1 08/21 3 200
> > 1 08/21 4 150
> > 2 ... ... ...
> > 2 ... ... ...
> > 3 ... ... ...
> > 4 ... ... ...
> > 4 ... ... ...
> >
> > The report is going to take Id and Date as inputs and report generated
> > is in following format
> >
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4
> > 100 500 200 150
> >
> > This can definetely be done creating a temp table and writing to this
> > temp table thru multiple selects for each itemtype.
> > Is there any better way'
> >
> > Thanks
> >