27,579
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
GO
CREATE TABLE #tab(
lsn VARCHAR(50),
columnName VARCHAR(20),
old_value varchar(20)
)
INSERT INTO #tab
SELECT '007B:00153:002','banner','1' UNION ALL
SELECT '007B:00153:002','OrderType',NULL UNION ALL
SELECT '007B:00153:002','remark','加急订单' UNION ALL
SELECT '007B:00153:005','banner','2' UNION ALL
SELECT '007B:00153:005','OrderType','1' UNION ALL
SELECT '007B:00153:005','remark','已完成订单'
--测试数据结束
SELECT
MAX(CASE columnName WHEN 'banner' THEN old_value ELSE NULL END) banner,
MAX(CASE columnName WHEN 'OrderType' THEN old_value ELSE NULL END) OrderType,
MAX(CASE columnName WHEN 'remark' THEN old_value ELSE NULL END) remark
FROM #tab
GROUP BY lsn
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
GO
CREATE TABLE #tab(
lsn VARCHAR(50),
columnName VARCHAR(20),
old_value varchar(20)
)
INSERT INTO #tab
SELECT '007B:00153:002','banner','1' UNION ALL
SELECT '007B:00153:002','OrderType',NULL UNION ALL
SELECT '007B:00153:002','remark','加急订单' UNION ALL
SELECT '007B:00153:005','banner','2' UNION ALL
SELECT '007B:00153:005','OrderType','1' UNION ALL
SELECT '007B:00153:005','remark','已完成订单'
--测试数据结束
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+columnName+']' from #tab for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [banner],[OrderType],[remark] from #tab pivot(max(old_value)for columnName in('+@name+'))a'
PRINT @sql
EXEC( @sql)