multiple columns per row?

Hello,
I have a table with data like the following:
testTable
--
name, cost, eventdate
--
a, 2, 2005-03-22 00:00:00.000
a, 3, 2005-03-23 00:00:00.000
a, 2, 2005-03-23 00:00:00.000
b, 2, 2005-03-23 00:00:00.000
I'd really like to be able to get the following:
result1
--
name, cost for eventdate1, eventdate1, cost for eventdate2, eventdate2
---
a, 2, 2005-03-22 00:00:00.000, 5, 2005-03-23 00:00:00.000
b, 2, 2005-03-23 00:00:00.000, NULL, NULL
The best I'm able to come up with is:
select t.name, SUM(t.cost) as 'sum(cost)', t.eventdate
from test t
group by name, t.eventdate
result2
--
name, sum(cost), eventdate
--
a, 2, 2005-03-22 00:00:00.000
a, 5, 2005-03-23 00:00:00.000
b, 2, 2005-03-23 00:00:00.000
Can anyone advise on how to get the result as shown in "result2"?
Thanks,
Craig.
....o00o( ",)o00o.....HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
Dynamic Crosstab Queries
http://www.windowsitpro.com/Article...15608.html?Ad=1
AMB
"Craig H." wrote:

> Hello,
> I have a table with data like the following:
> testTable
> --
> name, cost, eventdate
> --
> a, 2, 2005-03-22 00:00:00.000
> a, 3, 2005-03-23 00:00:00.000
> a, 2, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000
>
> I'd really like to be able to get the following:
> result1
> --
> name, cost for eventdate1, eventdate1, cost for eventdate2, eventdate2
> ---
> a, 2, 2005-03-22 00:00:00.000, 5, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000, NULL, NULL
>
> The best I'm able to come up with is:
> select t.name, SUM(t.cost) as 'sum(cost)', t.eventdate
> from test t
> group by name, t.eventdate
> result2
> --
> name, sum(cost), eventdate
> --
> a, 2, 2005-03-22 00:00:00.000
> a, 5, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000
>
> Can anyone advise on how to get the result as shown in "result2"?
> Thanks,
> Craig.
> --
> .....o00o( ",)o00o.....
>|||If you want a utility to do this sort of crosstab/pivoting quite
easily (spare yourself all the messy sql code:) check out the RAC utility
for S2k.
For your example see the @.rank parameter in the Help file.
RAC and QALite @.
www.rac4sql.net
"Craig H." <spam@.thehurley.com> wrote in message
news:%23yGRfeLMFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a table with data like the following:
> testTable
> --
> name, cost, eventdate
> --
> a, 2, 2005-03-22 00:00:00.000
> a, 3, 2005-03-23 00:00:00.000
> a, 2, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000
>
> I'd really like to be able to get the following:
> result1
> --
> name, cost for eventdate1, eventdate1, cost for eventdate2, eventdate2
> ---
> a, 2, 2005-03-22 00:00:00.000, 5, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000, NULL, NULL
>
> The best I'm able to come up with is:
> select t.name, SUM(t.cost) as 'sum(cost)', t.eventdate
> from test t
> group by name, t.eventdate
> result2
> --
> name, sum(cost), eventdate
> --
> a, 2, 2005-03-22 00:00:00.000
> a, 5, 2005-03-23 00:00:00.000
> b, 2, 2005-03-23 00:00:00.000
>
> Can anyone advise on how to get the result as shown in "result2"?
> Thanks,
> Craig.
> --
> ....o00o( ",)o00o.....