22,206
社区成员
发帖
与我相关
我的任务
分享
--测试数据
IF OBJECT_ID ('TempDB..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
dt DATETIME,
tagname VARCHAR(50),
pv int
)
INSERT INTO #tab
SELECT GETDATE(),'bbuilding1\unit3\id803',30228
UNION ALL
SELECT GETDATE(),'bbuilding1\unit3\t803',0
UNION ALL
SELECT GETDATE(),'bbuilding1\unit3\tsp803',19
UNION ALL
SELECT GETDATE(),'bbuilding1\unit3\te803',4099
UNION ALL
SELECT GETDATE(),'bbuilding1\unit3\tc803',41
UNION ALL
SELECT GETDATE(),'bbuilding1\unit3\tp803',5
--测试数据结束
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + LEFT(
RIGHT(tagname, CHARINDEX('\', REVERSE(tagname)) -1),
LEN(RIGHT(tagname, CHARINDEX('\', REVERSE(tagname)) -1)) -
3
) + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
SET @sql =
'with cte as (SELECT dt,pv,left(tagname,len(tagname)-len(right(tagname, charindex(''\'',reverse(tagname))-1)))+RIGHT(tagname,3) as newtabname,left(right(tagname, charindex(''\'',reverse(tagname))-1),LEN(right(tagname, charindex(''\'',reverse(tagname))-1))-3) AS title FROM #tab)'
SET @sql = @sql + 'SELECT * from cte pivot( max(pv) for title in(' + @name +
'))a'
--PRINT @sql
EXEC (@sql)
dt newtabname id t tc te tp tsp
----------------------- -------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
2017-09-14 17:07:41.717 bbuilding1\unit3\803 30228 0 41 4099 5 19
(1 行受影响)