sql server 一列的多行内容拼接成一行中的一列

select ProjName,status from Project;
--for xml path('') 是把得到的内容以XML的形式显示。
--stuff(param1, startIndex, length, param2) 将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。
select     
    ProjectName = (
        stuff(
            (select '、' + ProjName from Project where Status = A.Status 
            for xml path('')),
            1,
            1,
            ''
        )
    ) 
from Project as A group by status;

 

 

xml path('')用法这里也详解：https://www.cnblogs.com/yuer20180726/p/11365498.html

二、普通行转列

准备：

-- 创建模拟数据CREATE TABLE #TempSubjectResult(StudentName NVARCHAR(50) NOT NULL,StudentSubject NVARCHAR(50) NOT NULL,StudentGrade DECIMAL(4,1) NOT NULL)INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小姚','语文','80')INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小姚','数学','70')INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小姚','英语','60')INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小明','语文','90')INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小明','数学','95')INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES ('小明','英语','98')
SELECT * FROM #TempSubjectResult

  原数据结果：

 

 1、普通写法：

-- 1 Case When普通版
SELECT StudentName 学生名,
       MAX(CASE WHEN StudentSubject='语文' THEN StudentGrade END) 语文,
       MAX(CASE WHEN StudentSubject='数学' THEN StudentGrade END) 数学,
       MAX(CASE WHEN StudentSubject='英语' THEN StudentGrade END) 英语
  FROM #TempSubjectResult
 GROUP BY StudentName

2、PIVOT函数

SELECT * 
  FROM #TempSubjectResult 
  PIVOT(MAX(StudentGrade) FOR StudentSubject IN([语文],[数学],[英语])) T

 结果：

 

 

参照：https://www.cnblogs.com/kylan/p/10844414.html