一列SQL Server中的多行 [英] Multiple rows in one column SQL Server

问题描述

我想在一列中连接多行.我在Internet上找到了许多示例，但对我不起作用.我在做什么错了?

I want to concatenate multiple rows in one column. I found many examples in Internet but does not working for me. What am I doing wrong?

        SELECT UserID,  
        STUFF((SELECT '; ' + Email.Email From Email where  UserEmail.EmailID = Email.ID for xml path('')),1,1, '') AS Emails
        From UserEmail
        where UserID = 1

我仍然有这样的信息

UserID  Email
1       [e-mail]
1       [e-mail] 

-编辑-

好，我做了这个更改，但仍然有2行.如果我应用了distinct，这将解决我的问题，但是为什么我必须使用distinct.该查询必须对其进行单独分组.

Ok, I did this change but still having 2 rows. If I apply distinct this will fix my problem but why do I have to use distinct. The query must group it by itself.

        SELECT UserID,  
        STUFF(( SELECT '; ' + ea.Email 
                From Email ea inner join UserMail ue_inner on ue_inner.EmailID = ea.ID 
                where 
                ue_inner.UserID = ue.UserID
                for xml path('')), 1, 1, '') 
                AS Email
        From UserEmail ue
        where UserID = 1

结果

UserID   Email 
1        [e-mail]; [e-mail]
1        [e-mail]; [e-mail]

推荐答案

不幸的是，SQL Server没有正确的字符串连接，因此在分组时无法连接字符串.因此，有几种可能的解决方案.

Unfortunately, SQL Server doesn't have proper string concatenation, so you cannot concatenate strings while grouping. So there're several possible solutions.

1..如果要串联单个ID的数据，可以使用可变技巧:

1. If you want to concatenate data for single ID, you can use variable trick:

declare @Emails varchar(max)

select @Emails = isnull(@Emails + ', ', '') + E.Email
from Email as E
    inner join UserEmail as UE on UE.EmailID = E.EmailID
where UE.UserID = 1

select @Emails;

-----------------
[e-mail], [e-mail]

2..如果要进行正确的分组，可以使用 xml技巧.因此，基本思路是-您获得不同的UserID作为锚查询(使用group by)，然后在子查询中连接数据.重要的是要正确执行此操作，使用for xml ... type并将连接的字符串获取为.value()，这样特殊字符将被正确处理:

2. If you want to do proper grouping, you can use xml trick. So, basic idea is - you get distinct UserID as anchor query (using group by), and then concatenate data in the subquery. Important thing is to do this correctly, use for xml ... type and get concatenated string as .value(), so special characters will be treated correctly:

select
    UE.UserID,
    stuff(
        (
            select ', ' + TE.Email
            from Email as TE
                inner join UserEmail as TUE on TUE.EmailID = TE.EmailID
            where TUE.UserID = UE.UserID
            for xml path(''), type
        ).value('.', 'varchar(max)')
    , 1, 2, '') as Emails
from UserEmail as UE
group by UE.UserID

有关示例的sql小提琴演示: sql小提琴演示

See sql fiddle demo with examples: sql fiddle demo

这篇关于一列SQL Server中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！