SQL Server 行转列 列转行操作

 

1.多行转成一行(并以','分开)

表数据如下图:

 

查询结果如下图:

 

 SQL查询脚本:

SELECT addPer, house_code = (STUFF((SELECT ',' + house_code FROM my_demo WHERE addPer = T.addPer FOR XML PATH('')), 1, 1, ''))
FROM my_demo AS T
GROUP BY addPer

　　

 2.一行转为一列

SELECT STUFF((SELECT  ',' + CAST(id AS VARCHAR(10))  FROM(SELECT id FROM myDemo WHERE del = 0 ) a  FOR  XML PATH('')  ), 1,0, '') AS Name 

　　

 3.一列转多行

--创建表结构
CREATE TABLE #tempStudent(id INT,studentName VARCHAR(50),subjectName VARCHAR(MAX))

--模拟数据
INSERT INTO #tempStudent(id,studentName,subjectName) VALUES(1,'张三','语文,数学,英语,化学,政治,历史')
INSERT INTO #tempStudent(id,studentName,subjectName) VALUES(2,'李四','语文,数学,英语')
INSERT INTO #tempStudent(id,studentName,subjectName) VALUES(3,'王五','化学,政治,历史')

--查询语句
SELECT A.id,A.studentName,b.[value] AS 科目
FROM (
    SELECT id,studentName,[value]=CAST('<v>' + REPLACE(subjectName, ',','</v><v>') + '</v>' AS XML) 
    FROM #tempStudent
) A
OUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM A.[value].nodes('/v') AS T(C)) B

执行结果：