• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL challenge

sasguru

Better than AtW
Joined
Jul 6, 2005
Messages
36,703
Likes received
1,283
Thanks received
451
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?
 
Easy enough writing a program in SAS. But I don't believe you can't do it in SQL!
 
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.
 
G8_Summit said:
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 ....
 
dump the collated events to a temp table first then select on a join?
 
scotspine said:
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?
 
scotspine said:
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 ....
 
Last edited: 10:28
just loop through in sql and stitch them together
 
Since when did looping become part of SQL ??

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

Advertisers

Back
Top