I have following tables.
Student
table:
Id Name
1 A
2 B
Marks
table:
StudentId SubjectId Marks
1 1 67
1 2 89
2 1 78
2 2 86
I want to concat multiple rows for a given studentId and SubjectId, Marks columns into a single column as below.
Result:
Id Name SubjectMarks
1 A 1:67,2:89
2 B 1:78,2:86
I tried FOR XML PATH
. It works for a single column, but for 2 columns I couldn't write a query.
3条答案
按热度按时间xlpyo6sf1#
You can do this without a CTE:
6ju8rftf2#
SQL Fiddle
MS SQL Server 2008 Schema Setup:
Query 1:
Results:
368yc8dk3#
thanks alot. sir.It was very useful for me. gh.