Concatenation multiple rows and columns in SQL Server

Question

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.

Gordon Linoff · Accepted Answer · 2015-08-15 19:38:56Z

2

You can do this without a CTE:

select s.id, s.name,
       stuff((select ',' + cast(subjectid as varchar(8000)) + ':' + cast(marks as varchar(8000))
             from marks m
             where m.studentid = s.id
             for xml path (''), type
             ).value('.', 'varchar(max)'), 1, 1, ''
            ) as SubjectMarks
from student s;

answered Aug 15, 2015 at 19:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2015-08-15 19:25:28Z

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Student  (Id INT, Name VARCHAR(10))
INSERT INTO Student VALUES 
(1 ,'A'),
(2 ,'B')

 CREATE TABLE Marks  (StudentId INT , SubjectId INT , Marks INT)
INSERT INTO Marks VALUES 
(1     ,      1   ,    67),
(1     ,      2   ,    89),
(2     ,      1   ,    78),
(2     ,      2   ,    86)

Query 1:

;WITH cte AS 
 (
  SELECT StudentId 
       , CAST(SubjectId AS VARCHAR(10)) + ':' 
          + CAST(Marks AS VARCHAR(10)) AS SubjectMarks
  FROM Marks
 )
SELECT DISTINCT
       S.Id
      ,S.Name
      ,STUFF((SELECT ',' + SubjectMarks
              FROM cte
              WHERE x.StudentId = StudentId
              FOR XML PATH(''),TYPE)
              .value('.','Varchar(max)'),1,1,'') AS SubjectMarks
FROM cte x
INNER JOIN Student S ON S.Id = x.StudentId

Results:

| Id | Name | SubjectMarks |
|----|------|--------------|
|  1 |    A |    1:67,2:89 |
|  2 |    B |    1:78,2:86 |

Collectives™ on Stack Overflow

Concatenation multiple rows and columns in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related