如何使用SQL Server将行连接到一个单元格并在每行之后添加换行 [英] How to concatenate rows into one cell and add break line after each one using SQL Server

问题描述

我正在使用SQL Server 2017，并且正在尝试创建一个查询，该查询将每位员工的语言和熟练程度串联在一起.例如，将信息存储在我的SQL数据库中的表是这样的:

I am using SQL Server 2017 and I am trying to create a query that concatenates the languages and Levels of Proficiency in one line for every Employee. The table that stores the info in my SQL Database is this for example:

我想达到的最终结果是:

And the end result I would like to achieve is this:

使用Stuff函数和xml路径，我设法创建一个显示以下内容的选择查询:

Using Stuff function and xml path I have managed to create a select query that shows this:

但是我找不到插入中断线的方法.该查询将用作AspxGridview的数据源.

But I can't find a way to insert a break line. The query will be used as a datasource for an AspxGridview.

有帮助吗?

提前谢谢！

到目前为止，我的查询是

My query so far:

select distinct
p.PersonID,
STUFF
( (SELECT char(10) + l.Language+' ('+ (case  cvnl.Proficiency when  1 then 'Good'
                                                                    when 2 then 'Very Good'
                                                                    when 3 then 'Excellent'
                                                                    end )
                                                    +') ' FROM CV_NewLanguages cvnl
                                                            inner join Languages l on l.LanguageID = cvnl.LanguageID
    WHERE cvnl.PersonID = p.PersonID

    ORDER BY l.Language ASC FOR XML PATH('')), 1, 1, '') AS Languages
from CV_Certifications cv
       inner join person p on cv.PersonID=p.PersonID
             inner join CV_NewLanguages cvnl on cvnl.PersonID=p.PersonID
               inner join Languages l on l.LanguageID=cvnl.LanguageID
where  active=1      
group by 
p.PersonID,
cvnl.Proficiency,
l.Language
order by p.PersonID

结果是这样的: 在此处输入图片描述

推荐答案

在使用SQL Server 2017时，可以使用STRING_AGG函数

As you are using SQL server 2017 you can use STRING_AGG function

SELECT 
    p.PersonID, 
    STRING_AGG( Language + '(' + 
        CASE cvnl.Proficiency 
            WHEN 1 THEN 'Good'
            WHEN 2 THEN 'Very Good'
            THEN 3 THEN 'Excellent'
        END + ')'
    , CHAR(13) + CHAR(10)) AS Languages
FROM CV_Certifications cv
JOIN person p on cv.PersonID = p.PersonID
JOIN CV_NewLanguages cvnl on cvnl.PersonID = p.PersonID
JOIN Languages l on l.LanguageID = cvnl.LanguageID
WHERE active=1      
GROUP BY p.PersonID,
ORDER BY p.PersonID

您提到，AspxGridview需要它，因此也可能需要HTML中断

You mention that you need it for a AspxGridview, so you might need an HTML break too

SELECT 
    p.PersonID, 
    STRING_AGG( Language + '(' + 
        CASE cvnl.Proficiency 
            WHEN 1 THEN 'Good'
            WHEN 2 THEN 'Very Good'
            THEN 3 THEN 'Excellent'
        END + ')'
    , CHAR(13) + CHAR(10) + '<BR/>' + CHAR(13) + CHAR(10)) AS Languages
FROM CV_Certifications cv
JOIN person p on cv.PersonID = p.PersonID
JOIN CV_NewLanguages cvnl on cvnl.PersonID = p.PersonID
JOIN Languages l on l.LanguageID = cvnl.LanguageID
WHERE active=1      
GROUP BY p.PersonID,
ORDER BY p.PersonID

如果您使用的是SQL先前版本，或者想继续使用STUFF，则可以从STUFF的结果中替换逗号

If you are using an SQL previous version or you want to keep using STUFF you can REPLACE the comma from your STUFF's result

SELECT 
    p.PersonID,
    REPLACE(
        STUFF( (
            SELECT ',' + l.Language +' (' + 
            CASE cvnl.Proficiency 
                WHEN 1 THEN 'Good'
                WHEN 2 THEN 'Very Good'
                WHEN 3 THEN 'Excellent'
            END +') ' 
            FROM CV_NewLanguages cvnl
            JOIN Languages l on l.LanguageID = cvnl.LanguageID
            WHERE cvnl.PersonID = p.PersonID
            ORDER BY l.Language ASC 
            FOR XML PATH(''))
         , 1, 1, '')
    ,',',CHAR(13) + CHAR(10)) AS Languages
FROM person p  
WHERE EXISTS (SELECT 1 FROM CV_Certifications cv WHERE cv.PersonID = p.PersonID)
AND EXISTS (SELECT 1 FROM CV_NewLanguages cvnl WHERE cvnl.PersonID = p.PersonID
AND active=1      
ORDER BY p.PersonID

我还可以自由调整您的查询

I also took the freedom to tune your query a little

这篇关于如何使用SQL Server将行连接到一个单元格并在每行之后添加换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！