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?

Improve this question
5
  • 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. Commented Dec 20, 2010 at 22:37
  • Reminds me of MySQL's GROUP_CONCAT(). This question w.r.t. MSSQL help? stackoverflow.com/questions/273238/… Commented Dec 20, 2010 at 22:38
  • Check the following question responses that is similar to yours (click here) Commented Dec 20, 2010 at 22:41
  • 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" Commented Dec 20, 2010 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). Commented Dec 20, 2010 at 22:57

1 Answer 1

Reset to default
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?

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That's exactly what I was thinking. You can do this with dynamic SQL, but would you want to?
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.