SQL challenge

[sasguru]

I have data like this:

Name Event

Person1 Event1
Person1 Event2
Person1 Event3
Person2 Event1
Person2 Event2
etc

and I want to report it like this:

Name: Count of Events: Events

Person1 3 Event1/Event2/Event3
Person2 2 Event1/Event2
etc

How do I do this in SQL?

[G8_Summit]

You can't.

Try writing a program.

HTH.

[sasguru]

Easy enough writing a program in SAS. But I don't believe you can't do it in SQL!

[G8_Summit]

Well then first part is easy-

Select Name, count(*)
from TableName
Order By Name
Group By Name;

This will give you:

Person1 3
Person2 2

But I can't see how you would select multiple columns from the same row to get your list of event names.

[sasguru]

But I can't see how you would select multiple columns from the same row to get your list of event names.

Well yes that is the problem ....

[scotspine]

dump the collated events to a temp table first then select on a join?

[OrangeHopper]

dump the collated events to a temp table first then select on a join?

Don't you have top decide on a maximum number of events to do that?

[sasguru]

dump the collated events to a temp table first then select on a join?

But the question is how to collate (concatenate?) them in the first place ...

Edited:
Yes the number of events is unknown. Mind you its easy enough to get the max number of events by doing a count first and joining. Hmmm beginning to see a glimmer of how this might work ....

[scotspine]

just loop through in sql and stitch them together

[G8_Summit]

Since when did looping become part of SQL ??

Looping is programmatical - thats why I said use a program.