sql实现行转列

行转列不留空 

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