行合并，行转列，列转行

行合并

DECLARE @T1 table
(
UserID int ,
UserName nvarchar(50),
CityName nvarchar(50)
);

insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')

select * from @T1
--将列数据转 ： ,a,b,c,d,e
SELECT ',' + UserName FROM @T1  FOR XML PATH('')
--然后使用STUFF替换第一个逗号
-----最优的方式

SELECT A.CityName,STUFF((SELECT ',' + S.UserName FROM @T1 S WHERE S.CityName=A.CityName
 FOR XML PATH('')),1, 1, '') AS UserName
FROM @T1 A
GROUP BY A.CityName

--第一个字符串 abcdef 的第 2 个位置 (b) 开始删除三个字符，然后在删除位置插入第二个字符串，
--从而创建并返回一个字符串。
--SELECT STUFF('abcdef', 2, 3, 'T');  
--aTef 


----第二种方式
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1)
FROM (
  SELECT CityName,
    (SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH(''))   AS UserList
  FROM @T1 A
  GROUP BY CityName
) B

--stuff(select ',' + fieldname  from tablename for xml path('')),1,1,'')

行转列

参考https://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html https://www.cnblogs.com/snhc/p/6802486.html

--利用pivot函数
--table_source
--PIVOT(
--聚合函数（value_column）
--FOR pivot_column
--IN(<column_list>)
--)

--数据 
CREATE TABLE #tb(Name VARCHAR(10),Course VARCHAR(10),Core INT)

insert into #tb VALUES ('张三','语文',74)
insert into #tb VALUES ('张三','数学',83)
insert into #tb VALUES ('张三','物理',93)
insert into #tb VALUES ('李四','语文',74)
insert into #tb VALUES ('李四','数学',84)
insert into #tb VALUES ('李四','物理',94)

SELECT * FROM #tb ORDER BY  Name;
--方法一：行转列
SELECT Name,
 max(CASE Course WHEN'语文' THEN Core ELSE 0 END) 语文,
 max(CASE Course WHEN'数学' THEN Core ELSE 0 END) 数学,
 max(CASE Course WHEN'物理' THEN Core ELSE 0 END) 物理
FROM #tb
GROUP BY Name;
--方法二：行转列
SELECT * FROM #tb pivot(MAX(Core) FOR Course IN (语文,数学,物理))a;

DROP TABLE #tb;

注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别 在数据库属性->选项->兼容级别改为 90

列转行

 --数据 
CREATE TABLE #tb(Name VARCHAR(10),Course VARCHAR(10),Core INT)

insert into #tb VALUES ('张三','语文',74)
insert into #tb VALUES ('张三','数学',83)
insert into #tb VALUES ('张三','物理',93)
insert into #tb VALUES ('李四','语文',74)
insert into #tb VALUES ('李四','数学',84)
insert into #tb VALUES ('李四','物理',94)

SELECT * FROM #tb ORDER BY  Name;
------------------------行转列------------------------
--SELECT Name,
-- max(CASE Course WHEN'语文' THEN Core ELSE 0 END) 语文,
-- max(CASE Course WHEN'数学' THEN Core ELSE 0 END) 数学,
-- max(CASE Course WHEN'物理' THEN Core ELSE 0 END) 物理
--FROM #tb
--GROUP BY Name;

SELECT * INTO #T FROM #tb pivot(MAX(Core) FOR Course IN (语文,数学,物理))a;
SELECT * FROM #T;

------------------------方法一：列转行------------------------
--SELECT Name,Course,Core
--FROM  #T UNPIVOT ( Core FOR Course IN ( 语文, 数学, 物理 ) ) T
--;

------------------------方法二：列转行UNION ALL------------------------
SELECT * FROM
(
 SELECT Name,Course='语文',Core=语文 FROM #T
 UNION ALL
 SELECT Name,Course='数学',Core=数学  FROM #T
 UNION ALL
 SELECT Name,Course='物理',Core=物理 FROM #T
) t
ORDER BY Name
;
DROP TABLE #tb;
DROP TABLE #T;