Как объединить строки в одну ячейку и добавить строку разрыва после каждой с помощью SQL Server

Я использую SQL Server 2017 и пытаюсь создать запрос, который объединяет языки и уровни владения в одну строку для каждого сотрудника. Например, таблица, в которой хранится информация в моей базе данных SQL:

Исходный код таблицы

И конечный результат, которого я хотел бы достичь, таков:

Конечный результат

Используя функцию Stuff и путь xml, мне удалось создать запрос выбора, который показывает это:

Пока

Но я не могу найти способ вставить строку разрыва. Запрос будет использоваться в качестве источника данных для AspxGridview.

Любая помощь?

Заранее спасибо!

Мой запрос до сих пор:

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 sql-server sql-server-2017 string-aggregation
person Kate10    schedule 12.03.2019    source источник
comment
я пометил SQL Server, хотя SQL Server 2015 не является допустимым продуктом.   -  person Gordon Linoff    schedule 12.03.2019
comment
Нет SQL Server 2015 — у нас были 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 и скоро 2019 — так какой из них вы действительно используете?   -  person marc_s    schedule 12.03.2019
comment
Вы должны сделать резервную копию и опубликовать то, что вы в конечном итоге пытаетесь сделать. Это веб-приложение? То, что вы предлагаете, — это не то, с чем вы бы справились в SQL Server. SQL Server — это не Excel. То, что вы объясняете, очень похоже на логику презентации по отношению к набору данных, поэтому предоставление некоторого контекста поможет нам помочь вам.   -  person kevin_fitz    schedule 12.03.2019
comment
Я уже опубликовал то, что я в конечном итоге пытаюсь сделать. При чем тут эксель? Я использую devexpress gridvew, чтобы показать информацию об уровне языков сотрудников, и я пытаюсь вставить строку разрыва после каждого языка и уровня. Devexpress Gridview — это способ представления данных.   -  person Kate10    schedule 13.03.2019
comment
Я согласен с kevin_fitz. Вы должны сделать это на уровне представления, а не в SQL.   -  person a_horse_with_no_name    schedule 13.03.2019
comment
AspxGridview работает с SQLDatasources. Не могу изменить путь.   -  person Kate10    schedule 13.03.2019

Ответы (2)


arrow_upward
0
arrow_downward

Поскольку вы используете SQL Server 2017, вы можете использовать функцию STRING_AGG.

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

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.

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

Я также взял на себя смелость немного настроить ваш запрос

person Daniel Brughera    schedule 13.03.2019
comment
Если я использую CHAR(13) + CHAR(10), я получаю эти символы #x0D; . Также, если я использую ‹br/›, я также получаю символы и не разрываю строку. - person Kate10; 13.03.2019
comment
Если вы используете строки разрыва или теги в STUFF, да, вы получаете эти символы из-за XML PATH, но вы уже пробовали с STRING_AGG?? - person Daniel Brughera; 13.03.2019
comment
Спасибо за усилия. Попробовал предложенное вами выше решение, но после уровня квалификации по-прежнему нет линии разрыва. Я еще не пробовал с String_Agg... - person Kate10; 13.03.2019
comment
Если вы хотите увидеть линии разрыва в окне результатов в SSMS, вы их не увидите... проверьте в своем GridView - person Daniel Brughera; 13.03.2019
comment
И попробуйте также с ‹br/›, если нет... но я почти уверен, что это работает - person Daniel Brughera; 13.03.2019
comment
Попробовал ваше предложение в Gridview, и язык и уровень владения отображаются в одной строке. Я проверяю Gridview, а не SSMS. Спасибо хоть. - person Kate10; 13.03.2019
comment
Даже используя '‹BR/›'? ни за что! - person Daniel Brughera; 13.03.2019
comment
Это действительно сработало! Указанный столбец DevExpressGridview должен иметь свойство PropertiesTextEdit-EncodeHtml=false, чтобы код работал, с ‹br/› или без него. Будь ты проклят, DevExpress Gridview... Спасибо, Даниэль Бругера, за твою помощь и настойчивость!!! - person Kate10; 13.03.2019

arrow_upward
0
arrow_downward

Вы можете попробовать приведенное ниже решение от Pinal.

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

Я использовал это давным-давно, когда привязывал его вывод к кристаллическому отчету. Не пробовал с aspx gridview.

person Mohsin Aboobacker    schedule 12.03.2019
comment
Извините, пока мне это не помогло. - person Kate10; 13.03.2019