Creating "columns" from transaction data

Hi,
I have a transaction table that basically has the following fields
RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e.
quantities in multiple periods)
I need to convert an entire table of these records to one that looks like
this...
RedId , P1Qty, P2Qty, P3Qty, P4Qty etc...
Which has one row per RecId and places the quantity (quantities) in the
appropriate "period" column(s) based on the value(s) of "Period" in
the transaction file for each record.
I've done this before in Access, using the IIF function for each of P1...P4
columns (IIF(Period = 1, Quantity, 0), IIF(Period = 2, Quantity, 0) for
each of the columns of the derived table I was making. This doesn't seem to
work for SQL Server. IIF exists, but I can't get the computed columns to
work properly (Syntax error near "=").
So, I've thought about...
1) Use CreateTable to create my derived table with periods as columns,
2) Write a series of INSERT queries that reads the transaction file for
each possible individual value for "Period" and populates the appropriate
column in the derived table
3) Sum the derived table on every column by RecId
4) Run the whole batch as the SelectCommand of my DataAdapter. The last
command in the batch is Select * from DerivedTable and this is the table
that the DataSet gets.
There has to be a better way to do this?
Thanks.
BBMYou can use CASE expressions instead of IIF. But why would you ever create a
table like this? What you are asking for is a report not a table. Any
reporting tool will construct a cross tab report for you.
David Portas
SQL Server MVP
--|||Do you have any idea what First Normal Form is? You might want to
learn about RDBMS before you write any code.|||Thanks David, CASE was just what I'm looking for.
In this instance, this result set is used as one of the tables in
multi-table DataSet used on a fairly complex display.
Thanks again.
BBM
"David Portas" wrote:

> You can use CASE expressions instead of IIF. But why would you ever create
a
> table like this? What you are asking for is a report not a table. Any
> reporting tool will construct a cross tab report for you.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yes, in fact I do. I simplified the underlying data structure in my questio
n
to hopefully make it easier to reply to. I was only using the "extra" table
,
because I couldn't figure out how to get the result set I wanted in one pass
.
Thanks for your response anyway.
"--CELKO--" wrote:

> Do you have any idea what First Normal Form is? You might want to
> learn about RDBMS before you write any code.
>

Create View

Hi ,
If I create several views from the transaction table for reporting
purpose. Do the view occupy the disk space as well ?
Travis Tan
No. Unless it's an indexed view which contains data for the defined index.
Index views are sometimes used to speed up queries that require joins.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:0FD3F4E1-DFC2-42AB-8E29-325438FFE11F@.microsoft.com...
> Hi ,
> If I create several views from the transaction table for reporting
> purpose. Do the view occupy the disk space as well ?
> --
> Travis Tan

Create View

Hi ,
If I create several views from the transaction table for reporting
purpose. Do the view occupy the disk space as well ?
Travis TanNo. Unless it's an indexed view which contains data for the defined index.
Index views are sometimes used to speed up queries that require joins.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:0FD3F4E1-DFC2-42AB-8E29-325438FFE11F@.microsoft.com...
> Hi ,
> If I create several views from the transaction table for reporting
> purpose. Do the view occupy the disk space as well ?
> --
> Travis Tan