I'm writing an export function, where I need to export contacts to Excel, and I've run into a technical snag - or perhaps a gap in my SQL skills is closer to the truth. ;)
Here's the scenario: I've got a bunch of contacts in a database. Each contact can have many different roles, for example a contact can be both C# Developer and DBA, or DBA and IT-manager. These are split into three tables, like so:
------------------- ------------------- -------------------
* Contact * * ContactRole * * Role *
------------------- ------------------- -------------------
* ID * * ContactID * * ID *
* Name * * RoleID * * Name *
* Address * ------------------- -------------------
-------------------
Not too hard to follow. There's a set of contacts, and a set of roles. These are joined by the ContactRole table on the respective IDs.
When I export the contacts, I need to have a column in the export with all the roles comma separated, like C# Developer, DBA
or DBA, IT-manager
.
The export will be done from ASP.NET/C# codebehind, so I figured I could do this in code should it come to that, but I've got a feeling it's possible to do in the SQL.
The data comes from SQL Server 2005.