sql行转列，统计分类

表结构 

 

--求和后，按照类型行转列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(SensorTypeName) FROM (select top 100 SensorTypeName from SM_SensorType where SensorTypeID<>1019 order by DataType,Sort) as a
SET @sql_str = '
SELECT * FROM (select MineID,MineName,SensorTypeName,count(SensorID) as num
from V_RealtimeData group by MineID,MineName,SensorTypeName) /*数据源*/
AS P
PIVOT
(
SUM(num/*行转列后 列的值*/) FOR
p.SensorTypeName/*需要行转列的列*/ IN ('+ @sql_col +')
) AS T
'

PRINT (@sql_str)
EXEC (@sql_str)

 运行结果