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.
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')
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
╔═════════════╦══════╦═════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗
║ StudentIdId ║ Name ║ University ║ Department ║ Status ║
╠═════════════╬══════╬═════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣
║ 1 ║ John ║ Cambridge, Berkeley, Boston ║ Computer, Mathematic, Economy ║ Graduated, Graduated, Ongoing ║
║ 2 ║ Pete ║ Cambridge, Berkeley ║ Computer, Mathematic ║ Graduated, Graduated ║
╚═════════════╩══════╩═════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝