34,597
社区成员
发帖
与我相关
我的任务
分享
WITH table1(name,WorkDays,WeekNO)AS(
SELECT '吴江市治安卡口设备项目',5,1 UNION ALL
SELECT '吴江市治安卡口设备项目',4,2 UNION ALL
SELECT '吴江市治安卡口设备项目',4,3 UNION ALL
SELECT '吴江市治安卡口设备项目',3,4
)
SELECT *
INTO #temp
FROM table1
DECLARE @sql varchar(max)
DECLARE @columns varchar(max)
SET @columns = ''
SELECT @columns = @columns+',['+Convert(varchar(11),WeekNO)+']'
FROM #temp
SET @columns = STUFF(@columns,1,1,'')
SET @sql = '
SELECT *
FROM #temp
PIVOT (
MAX(WorkDays)
FOR WeekNO IN ('+@columns+')
) p'
PRINT @sql
EXEC(@sql)
SELECT *
FROM #temp
PIVOT (
MAX(WorkDays)
FOR WeekNO IN ([1],[2],[3],[4])
) p
name 1 2 3 4
---------------------- ----------- ----------- ----------- -----------
吴江市治安卡口设备项目 5 4 4 3