USE test GO -->生成表tbif object_id('tb') is not null drop table tb Go Create table tb([typename] nvarchar(2),[area] nvarchar(4),[name] nvarchar(3)) Insert into tb Select N'白班',N'黄泥磅区',N'李1' Union all Select N'白班',N'黄泥磅区',N'李2' Union all Select N'晚班',N'渝北区',N'李3' Union all Select N'白班',N'沙坪坝区',N'员工1' Union all Select N'白班',N'渝北区',N'员工2' DECLARE @sql NVARCHAR(MAX) SELECT @sql=ISNULL(@sql+',','')+'MAX(CASE WHEN area=N'''+area+''' THEN name ELSE '''' END) AS ['+area+']' FROM tb GROUP BY area ORDER BY GetDate() EXEC (' SELECT typename ,'+@sql+' FROM ( SELECT ROW_NUMBER()OVER(PARTITION BY typename,area ORDER BY name) AS row,* FROM tb ) AS t GROUP BY row,typename ')/* typename 沙坪坝区 渝北区 黄泥磅区 -------- ---- ---- ---- 白班 员工1 员工2 李1 白班 李2 晚班 李3 */
GO
-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([typename] nvarchar(2),[area] nvarchar(4),[name] nvarchar(3))
Insert into tb
Select N'白班',N'黄泥磅区',N'李1'
Union all Select N'白班',N'黄泥磅区',N'李2'
Union all Select N'晚班',N'渝北区',N'李3'
Union all Select N'白班',N'沙坪坝区',N'员工1'
Union all Select N'白班',N'渝北区',N'员工2'
DECLARE @sql NVARCHAR(MAX)
SELECT
@sql=ISNULL(@sql+',','')+'MAX(CASE WHEN area=N'''+area+''' THEN name ELSE '''' END) AS ['+area+']'
FROM tb GROUP BY area
ORDER BY GetDate()
EXEC ('
SELECT
typename
,'+@sql+'
FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY typename,area ORDER BY name) AS row,* FROM tb
) AS t
GROUP BY row,typename
')/*
typename 沙坪坝区 渝北区 黄泥磅区
-------- ---- ---- ----
白班 员工1 员工2 李1
白班 李2
晚班 李3
*/