How to convert multiple rows to one row in SQL Server?

Question

I have a problem with a query. Let's say I have two tables named PersonInfo and PersonEducation. I applied join operation on these tables with StudentId and I have a result like that.

   StudentIdId      Name    University    Department     Status
   ---------------------------------------------------------------
      1             John    Cambridge     Computer       Graduated
      1             John    Berkeley      Mathematic     Graduated
      1             John    Boston        Economy        Ongoing

It is just one example of a student (John). It shows that John graduated from 2 university and still studying in one university. University numbers can change depending on the students. My question is that how can I show these 3 row in just 1 row. I mean I want to show all education information in one row in order not to have multiple rows for one person.

Thanks in advance for your help.

Answer 1

Test Data

DECLARE @TABLE TABLE (StudentIdId INT, Name VARCHAR(100), University VARCHAR(100)
                       , Department VARCHAR(100),[Status] VARCHAR(100))
INSERT INTO @TABLE VALUES 
(1 ,'John','Cambridge','Computer'  ,'Graduated'),
(1 ,'John','Berkeley' ,'Mathematic','Graduated'),
(1 ,'John','Boston'   ,'Economy'   ,'Ongoing'),
(2 ,'Pete','Cambridge','Computer'  ,'Graduated'),
(2 ,'Pete','Berkeley' ,'Mathematic','Graduated')

Query

SELECT t.StudentIdId
      ,t.Name
      ,STUFF((SELECT ', ' + University 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS University
      ,STUFF((SELECT ', ' + Department 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS Department
      ,STUFF((SELECT ', ' + [Status] 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS [Status]

FROM @TABLE t 
GROUP BY t.StudentIdId ,t.Name

Result

╔═════════════╦══════╦═════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗
║ StudentIdId ║ Name ║         University          ║          Department           ║            Status             ║
╠═════════════╬══════╬═════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣
║           1 ║ John ║ Cambridge, Berkeley, Boston ║ Computer, Mathematic, Economy ║ Graduated, Graduated, Ongoing ║
║           2 ║ Pete ║ Cambridge, Berkeley         ║ Computer, Mathematic          ║ Graduated, Graduated          ║
╚═════════════╩══════╩═════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝

Answer 2

It depends on what you want for the output.

1. If you want to aggregate the data in PersonEducation to count the statuses, then you could join on a subquery of the PersonEducation table. There are numerous articles on how to use subqueries and how to group by fields.

Something like:

SELECT pere.StudentId
    , pere.StudentName
    , peri.UniversityCount
    , peri.GraduatedStatusCout
    , peri.OngoingStatusCount
FROM PersonInfo peri
LEFT JOIN
    (SELECT StudentId
        , UniversityCount = COUNT(*)
        , GraduatedStatusCount = SUM(IIF(Status = 'Graduated', 1, 0))
        , OngoingStatusCount = SUM(IIF(Status = 'Ongoing', 1, 0))
    FROM PersonEducation
    GROUP BY StudentId) pere
    ON peri.StudentId = pere.StudentId;

2. If you want to get the last university attended, then you would need a date attended field of some sort. It is possible there are more than one universities that someone attends at the same time, so you would need rules around how to pick a winner. In these cases, though, you could get away with using a ROW_NUMBER() in an OVER() clause to order the data and filter the results.

3. You could concatenate the data together as described here or in any number of other articles.