如何使用逗号合并SQL表值 [英] How to merge SQL table value using comma
问题描述
我有一个sql表,看起来像:
SQL查询:
选择*来自费用
输出看起来像: -
学生ID - 月份
101 - 3
101 - 4
101 - 5
102 - 1
102 - 2
但我需要输出如下: -
学生ID - 月
101 - 3, 4,5
102 - 1,2
我怎样才能使用sql查询。
我尝试过:
I have a sql table that look like:
SQL Query:
Select * from Fee
Output look like :-
StudentID - Month
101 - 3
101 - 4
101 - 5
102 - 1
102 - 2
but i need output like:-
StudentID - Month
101 - 3,4,5
102 - 1,2
how can i do it using sql query.
What I have tried:
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Convert(nvarchar(8),Month) from fees where studentid in (101,102)
group by studentid,month
SELECT @listStr
i我正在尝试此查询,但输出提供此查询,如:
Month
3,4,5,1,2
i am trying this query but the output give this query like:
Month
3,4,5,1,2
推荐答案
尝试:
Try:
SELECT StudentId, Months =
STUFF((SELECT ', ' + CONVERT(NVARCHAR(2), [Month])
FROM FEES f
WHERE f.StudentId = a.StudentID
FOR XML PATH ('')), 1, 2, '')
FROM Fees a
GROUP BY StudentId
这篇关于如何使用逗号合并SQL表值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!