SQL数据透视表动态

在这里，您将选择一列中的值以在数据透视图中显示为一列

DECLARE @cols NVARCHAr (MAX)SELECt @cols = COALESCE (@cols + ',[' + AvJT + ']', '[' + AvJT + ']')    FROM    (SELECt DISTINCT AvJT FROM YourTable) PV      ORDER BY AvJT

现在旋转查询

DECLARE @query NVARCHAr(MAX)SET @query = 'SELECt * FROM   (      SELECt date_1, StartHour,AvJT, data_source       FROM YourTable  ) x  PIVOT   (      -- Values in each dynamic column      SUM(data_source)      FOR AvJT IN (' + @cols + ') ) p;'EXEC SP_EXECUTESQL @query

• 点击这里查看结果

如果要对列名称不是动态的地方执行此操作，则可以执行以下查询

SELECt DATE_1,STARTHOUR,MIN(CASE WHEN AvJT='00001a' THEN data_source END) [00001a],MIN(CASE WHEN AvJT='00002a' THEN data_source END) [00002a],MIN(CASE WHEN AvJT='00003a' THEN data_source END) [00003a],MIN(CASE WHEN AvJT='00004a' THEN data_source END) [00004a]FROM YOURTABLEGROUP BY  DATE_1,STARTHOUR

• 点击这里查看结果

编辑 ：

我正在为您更新的问题进行更新。

声明一个变量进行过滤

data_source

DECLARE @DATASOURCE VARCHAr(20) = '1'

代替

QUOTENAME

DECLARE @cols NVARCHAr (MAX)SELECt @cols = COALESCE (@cols + ',[' + link_ID + ']', '[' + link_ID + ']')    FROM    (SELECt DISTINCT link_ID FROM C1_May_Routes WHERe data_source=@DATASOURCE) PV      ORDER BY link_ID

现在枢纽

DECLARE @query NVARCHAr(MAX)SET @query = 'SELECt * FROM   (      -- We will select the data that has to be shown for pivoting      -- with filtered data_source      SELECT date_1, StartHour,AvJT, link_ID      FROM C1_May_Routes      WHERe data_source = '+@DATASOURCE+'  ) x  PIVOT   (      -- Values in each dynamic column      SUM(AvJT)      -- Select columns from @cols       FOR link_ID IN (' + @cols + ') ) p;'EXEC SP_EXECUTESQL @query

• 点击这里查看结果