sql实现行转列

t1292582463

行转列不留空

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