TSQL 逗号分隔 [英] TSQL Comma Separation
问题描述
我正在编写一个导出函数，我需要将联系人导出到 Excel，但我遇到了技术障碍 - 或者我的 SQL 技能差距可能更接近事实.;)
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. ;)
场景如下:我在数据库中有一堆联系人.每个联系人可以有许多不同的角色，例如联系人可以是 C# 开发人员和 DBA，或者 DBA 和 IT 经理.它们分为三个表，如下所示:
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 * ------------------- -------------------
-------------------
不难理解.有一组联系人和一组角色.这些由 ContactRole 表连接到各自的 ID.
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.
当我导出联系人时，我需要在导出中有一列用逗号分隔的所有角色，例如 C# Developer, DBA 或 DBA, IT-manager.导出将在 ASP.NET/C# 代码隐藏中完成，所以我想我可以在代码中做到这一点，但我觉得可以在 SQL 中做到这一点.
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.
数据来自 SQL Server 2005.
The data comes from SQL Server 2005.
推荐答案
试试这个
declare @Roles nvarchar(max)
select @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
from Role
inner join ContactRole on Role.ID = ContactRole.RoleID
where ContactRole.ContactID = @ContactID
select @Roles
更新:
以上代码涵盖了单个联系人的功能.您可以创建一个带有参数 @ContactID 的标量函数，并从
Above code covers functionality for a single contact. You can create a scalar function with parameter @ContactID and call the function from a
Select Name, dbo.GetContactRoles(ID) From Contact
这篇关于TSQL 逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
