如何在SQL Server中将多行转换为一行? [英] How to convert multiple rows to one row in SQL Server?

问题描述

查询有问题.假设我有两个名为PersonInfo和PersonEducation的表.我用StudentId在这些表上应用了联接操作，结果是这样的.

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

这只是学生(约翰)的一个例子.它显示约翰毕业于2所大学，并且仍在一所大学学习.大学人数可能会因学生而异.我的问题是，我如何只显示1行中的这3行.我的意思是我想将所有教育信息显示在一行中，以免一个人有多行.

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.

预先感谢您的帮助.

推荐答案

测试数据

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          ║
╚═════════════╩══════╩═════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝

这篇关于如何在SQL Server中将多行转换为一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！