行转列不留空
SELECT ‘A‘ AS [订单号],‘四川‘ AS [地址] INTO #Result
INSERT INTO #Result
SELECT ‘A‘ AS [订单号],‘成都‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘新疆‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘喀什‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘和田‘ AS [地址]
SELECT CAST(ROW_NUMBER() OVER(PARTITION BY [订单号] ORDER BY [地址]) AS NVARCHAR(256)) AS num,[订单号],[地址] INTO #Table FROM #Result GROUP BY [订单号],[地址]
DECLARE @sql NVARCHAR(MAX)
set @sql = ‘select [订单号] ‘
select @sql = @sql + ‘ , max(case [num] when ‘‘‘ + num + ‘‘‘ then [地址] else ‘‘‘‘ end) [地址]‘ from (select DISTINCT num FROM #Table) as a
set @sql = @sql + ‘ from #Table group by [订单号]‘
EXEC (@sql)
DROP TABLE #Result
DROP TABLE #Table
行转列,行做列头
SELECT ‘A‘ AS [订单号],‘四川‘ AS [地址] INTO #Result
INSERT INTO #Result
SELECT ‘A‘ AS [订单号],‘成都‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘新疆‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘喀什‘ AS [地址]
UNION ALL
SELECT ‘B‘ AS [订单号],‘和田‘ AS [地址]
SELECT * FROM #Result
DECLARE @sql NVARCHAR(MAX)
set @sql = ‘select [订单号] ‘
select @sql = @sql + ‘ , max(case [地址] when ‘‘‘ + [地址] + ‘‘‘ then [地址] else ‘‘‘‘ end) [‘+[地址]+‘]‘ from (select DISTINCT [地址] FROM #Result) as a
set @sql = @sql + ‘ from #Result group by [订单号]‘
EXEC (@sql)
DROP TABLE #Result
原文：https://www.cnblogs.com/panguan/p/9679907.html