开发者论坛

 找回密码
 注册 (请使用非IE浏览器)
查看: 7834|回复: 0

sql实现行转列

[复制链接]

0

精华

597

贡献

100

赞扬

赞助者组

Rank: 14Rank: 14Rank: 14Rank: 14

帖子
18
软币
316
在线时间
18 小时
注册时间
2017-8-3
发表于 2020-10-15 17:45:00 | 显示全部楼层 |阅读模式
行转列不留空
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

回复

使用道具 举报

Archiver|手机版|小黑屋|开发者网 ( 苏ICP备08004430号-2 )
版权所有:南京韵文教育信息咨询有限公司

GMT+8, 2024-3-28 17:21

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表