How to concatenate rows into one cell and add break line after each one using SQL Server

Question

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:

Table source

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

End Result

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

So far

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

Any help?

Thank you in advance!

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

The result is this: enter image description here

i tagged with SQL Server even though SQL Server 2015 is not a valid product. — Gordon Linoff, Mar 12 '19 at 19:35
There is **no** SQL Server **2015** - we had 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 and soon 2019 - so which one are you **really** using? — marc_s, Mar 12 '19 at 20:22
You should back up a bit and post what you are ultimately trying to do. Is this a web application? What you're suggesting is not something you would handle "in" SQL Server. SQL Server is not excel. What you're explaining sounds a lot like Presentation Logic against a data set, so giving some context will help us help you. — kevin_fitz, Mar 12 '19 at 20:57
I have posted what I am ultimately trying to do already. What excel has to do with this? I am using devexpress gridvew to show info about the languages level of Employees and I am trying to insert a break line after every language and level. Devexpress Gridview is a way of presenting data. — Kate10, Mar 13 '19 at 07:59
I agree with kevin_fitz. You should do that in the presentation layer not in SQL — , Mar 13 '19 at 08:29
AspxGridview works with SQLDatasources. Can't change the way. — Kate10, Mar 13 '19 at 08:58

Mohsin Aboobacker · Answer 1 · 2019-03-12T23:51:15.367

0

You can try the below solution from Pinal.

https://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

I had used this long back while binding its output to a crystal report. Havent tried with an aspx gridview.

edited Mar 12 '19 at 23:51

answered Mar 12 '19 at 23:42

Mohsin Aboobacker

69
7

Sorry, it hasn't helped me so far. – Kate10 Mar 13 '19 at 10:28

Daniel Brughera · Accepted Answer · 2019-03-13T10:27:54.037

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

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

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

If I use CHAR(13) + CHAR(10) I get these symbols #x0D; . Also if I use
I also get symbols and no line break. — Kate10, Mar 13 '19 at 09:26
If you use break lines or tags into the STUFF yes, you get those symbols because XML PATH, but did you already try with STRING_AGG?? — Daniel Brughera, Mar 13 '19 at 10:04
Thank you for the effort. Tried the above solution you offered but still no break line after Proficiency Level. I have not tried with String_Agg yet... — Kate10, Mar 13 '19 at 10:26
If you want to see the break lines in your result window in SSMS you wont see them... check in your GridView — Daniel Brughera, Mar 13 '19 at 10:32
And try also with the
if not... but I'm pretty sure that it works — Daniel Brughera, Mar 13 '19 at 10:33
Tried you suggestion in a Gridview and the language and Proficiency are shown in the same row. I am checking the Gridview not the SSMS. Thanks though. — Kate10, Mar 13 '19 at 12:16
It actually worked! The specified DevExpressGridview Column needs to have PropertiesTextEdit-EncodeHtml="false" in order to make the code to work, with
or not. Damn you DevExpress Gridview... Thank you Daniel Brughera for your help and persistence!!! — Kate10, Mar 13 '19 at 13:45

How to concatenate rows into one cell and add break line after each one using SQL Server

2 Answers2

Related