SQL challenge

[f_eriksen]

although i think a couple of the previous posts have covered it, here is my take

if the nr of possible events are know, ie yuo know there will be evt1,evt2 and evt3, you can do this quite simple using setbased (and standard) sql.

select person,
sum(case when event_id = 1 then 1 else 0 end) as cnt_event_1,
sum(case when event_id = 2 then 1 else 0 end) as cnt_event_2,
sum(case when event_id = 3 then 1 else 0 end) as cnt_event_3
from mytable
group by person

if it is not, and you are not using ms sql 2005 (which implements crosstab), or ms sql 2000 with an additional 3rd party crosstab xp, then you can either:

a) build a dynamic sql t obe executed via exec(@sql), or sp_executesql (ms specific). no need for cursors even, d oa coalesce with distinct to pick up the exact nr of events to match...

b) use a cursor (which almost always sucks, and tells you u probably did somethin wrong)

either way, the db will sort it out faster then taking the data out of there and process in some other language (i dont care what u guys say, datacentric processing win 9 out of 10 times).

need specifics, let me know

my 2 cents only,

FGE

[MarillionFan]

Run it through Crystal Reports. Then it's a piece of piss. 2 minute job. Nah. 1 minute.

But then again, I doubt anyone would know how to even do that on here.

[scotspine]

only ever use cr if the client wants it used. used it a few weeks a go for similar crosstab and other queries but not my weapon of choice i have to say. lack of control over format etc etc

[sasguru]

Run it through Crystal Reports. Then it's a piece of piss. 2 minute job. Nah. 1 minute.

But then again, I doubt anyone would know how to even do that on here.

You must be gay.

[MarillionFan]

Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.

Yep. Gay for £400 a day thanks.

[scotspine]

ok arkwright, but do you do chintz drapes as well?

[expat]

Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.

Ahem, are you sure you're really investing your talents in the leading edge here?

[sasguru]

Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.

Yep. Gay for £400 a day thanks.

Well Interior designers earns lots of money too, but they're still gay. CR is a gay technology, full stop, not for real men.

[MarillionFan]

I assume by 'Real Men' your discussing David Beckham

[Mordac]

If you're that good why have you now had 4 contracts already this year and we're just halfway through February?

How's the daytime TV gig going, btw?