2

Possible Duplicate:
Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

I have a table of "Events", and each event has a a list of 1-4 (essentially variable #) "Users". So let's say I get all events for today, and then I want to list the users as a dynamic number of columns, rather than repeated rows.

Right now I have

SELECT E.EventID, E.Time, U.Name FROM Events
INNER JOIN Users U ON E.UserID = U.UserID
WHERE Date = '12/20/2010'

This brings me results like:

EventID, Time, Name
211, '4:00am', 'Joe'
211, '4:00am', 'Phil'
211, "4:00am', 'Billy'
218, '7:00am', 'Sally'
218, '7:00am', 'Susan'

I can work with this and it's acceptable, however the duplication for EventID and Time (there are more columns in my actual query) seems wasteful to me. What I would really like in the output is this:

EventID, Time, Name1, Name2, Name3
211, '4:00am', 'Joe', 'Phil', 'Billy'
218, '7:00am', 'Sally', 'Susan', NULL

I have tried looking at tutorials for PIVOTs (I have SQL 2008), but I don't know if they conceptually match what I'm trying to do. Most of them are using "MIN" or "MAX".

Maybe this can't be done? My other alternative is to get a list of Events for today, and then loop through that, finding a recordset of Users for that Event. I would prefer to grab it all in one query though.

Any ideas?

Community
  • 1
  • 1
MikeMurko
  • 2,214
  • 1
  • 27
  • 56
  • Can you change the schema of the table? I'd store `EventID Time Name1 Name2 Name3` in `Events`, then do a simple select on the eventID that each user has. Users could have multiple events, and eventIDs are then foreign keys. – Robert Dec 20 '10 at 22:37
  • Reminds me of MySQL's GROUP_CONCAT(). This question w.r.t. MSSQL help? http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-mssql – Riedsio Dec 20 '10 at 22:38
  • You could coalesce the name's together into a single delimited column. So you'd get: EventID, Time, Names 211, '4:00am', "Joe,Phil,Billy" – Adam Dec 20 '10 at 22:46
  • I think it's a wrong approach to use an iterating function in sql to accomplish this, you'll get a performance overhead that should have dealt with code iteration. and the answer you'll have will not be usable as is unless separated again by code (not to mention duplicates). – shevski Dec 20 '10 at 22:57
  • [Check the following question responses that is similar to yours (click here)](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery) – tawman Dec 20 '10 at 22:41

1 Answers1

2

Returning a variable number of columns would be harder to deal with in code. The result set you are currently getting is easy to transform into an object that you can worth with in code.

What are you trying to do with the output?

Lee Baker
  • 562
  • 1
  • 3
  • 12
  • That's exactly what I was thinking. You can do this with dynamic SQL, but would you want to? – Mark SQLDev Dec 20 '10 at 22:42
  • Well, it's just that there will potentially be ... say 200-300 rows coming back in that query. This page will be getting called a LOT, so I wanted to try minimize the amount of data I'm grabbing in each query. – MikeMurko Dec 20 '10 at 22:47