按列名MSSQL进行组查询

I have a table with the following schema

我有一个包含以下架构的表

But i need to export that info to another table but the result that i need must be like this

但我需要将该信息导出到另一个表，但我需要的结果必须是这样的

where machine value must be from the name of one of the column names everything must be for Sql Server

其中机器值必须来自其中一个列名的名称，一切都必须是Sql Server

1 个解决方案

#1

using cross apply() with values() to unpivot your data:

使用带有值的交叉apply（）来取消数据的取消：

  select v.Machine, v.Temperature, v.Humidity, t.Fecha
  from t
    cross apply (values ('DR673',DR673_T,DR673_H),('DR677',DR677_T,DR677_H)
      ) as v(Machine, Temperature, Humidity)

To dynamically generate the sql based on column names:

要根据列名动态生成sql：

declare @cols nvarchar(max), @sql nvarchar(max);

set @cols = stuff((
   select distinct 
      ',(''' + left(C.name,charindex('_',c.name)-1) 
      + ''','+ quotename(C.name) 
      + ','+ quotename(left(C.name,charindex('_',c.name))+'H') 
      +')'
   from sys.columns c
   where c.object_id = object_id('dbo.t')
     and c.name like '%_T'
   for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'');

set @sql = '
select v.Machine, v.Temperature, v.Humidity, t.Fecha
from t
  cross apply (values '+@cols+'
  ) as v(Machine, Temperature, Humidity)';

  select @sql as CodeGenerated;
  --exec sp_executesql @sql; /* to execute the dynamic sql */

rextester demo: http://rextester.com/NAOT80053

rextester演示：http：//rextester.com/NAOT80053

returns:

收益：

+---------------------------------------------------------------------------------------+
|                                     CodeGenerated                                     |
+---------------------------------------------------------------------------------------+
|     select v.Machine, v.Temperature, v.Humidity, t.Fecha                              |
|     from t                                                                            |
|       cross apply (values ('DR673',[DR673_T],[DR673_H]),('DR677',[DR677_T],[DR677_H]) |
|       ) as v(Machine, Temperature, Humidity)                                          |
+---------------------------------------------------------------------------------------+

#1