我使用的是SQL Server 2017,我正在尝试创建一个查询,将每个员工的语言和熟练程度在一行中串联起来。在我的SQL数据库中存储信息的表如下所示:
我想要实现的最终结果是:
使用Stuff函数和xml路径,我成功地创建了一个select查询,它显示了以下内容:
但是我找不到一种方法来插入一条断线。该查询将用作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
发布于 2019-03-13 16:18:21
由于您使用的是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 break
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以前的版本,或者您想继续使用内容,则可以从内容的结果中替换逗号
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
我还对您的查询进行了一些调整
发布于 2019-03-13 07:42:15
你可以尝试以下来自Pinal的解决方案。
当我把它的输出绑定到一个水晶报表时,我使用了这个很久以前的方法。还没有尝试过使用aspx网格视图。
https://stackoverflow.com/questions/55129372
复制相似问题