在SQL Server中将一个类别转换为列

Question

在SQL Server中将一个类别转换为列

4

我有一个查询，它会按类别给出一些属性作为结果，但我需要将每个类别转换为列

这是我的查询：

SELECT hist_statusevents.eqmt,
       hist_exproot.shiftdate,
       hist_statusevents.category, 
       Sum(hist_statusevents.duration/3600) as Value
FROM Powerview.dbo.hist_eqmtlist hist_eqmtlist, 
     Powerview.dbo.hist_exproot hist_exproot,   
      Powerview.dbo.hist_statusevents hist_statusevents
WHERE hist_exproot.shiftindex = hist_statusevents.shiftindex And 
      hist_statusevents.shiftindex = hist_eqmtlist.shiftindex And 
      hist_statusevents.eqmt = hist_eqmtlist.eqmtid And 
      hist_statusevents.eqmt like 'MOTO%'
GROUP BY hist_statusevents.eqmt, 
         hist_exproot.shiftdate,
         hist_statusevents.category

这是查询的输出结果：

eqmt            shiftdate         category   Value
MOTO705         2011-01-22 00:00:00  5      13,9597222805023
MOTO706         2011-01-28 00:00:00  3      0,280277773737907
MOTO704         2011-02-17 00:00:00  6      8,92749977111816
MOTO705         2011-02-09 00:00:00  6      10,07972240448
MOTO703         2011-03-15 00:00:00  1      22,6561107933521
MOTO704         2011-03-11 00:00:00  5      24
MOTO706         2011-01-27 00:00:00  1      9,95361125469208
MOTO703         2011-03-16 00:00:00  6      3,79916667938232
MOTO704         2011-01-08 00:00:00  6      24

但我需要得到这样的结果：

eqmt            shiftdate            1  2  3  4  5  6  7
MOTO706         2011-01-28 00:00:00  values for each category
MOTO704         2011-02-17 00:00:00  
MOTO705         2011-02-09 00:00:00  
MOTO703         2011-03-15 00:00:00

我一直在尝试使用选择语句，但我无法使结构正常工作。

- Pipebritop

有限制和固定的类别数量吗？ - Ricardo Pontual

1

不良习惯：使用旧式JOIN - 旧式的“逗号分隔表列表”样式已被正确的ANSI JOIN语法所取代，这是在20多年前的ANSI-92 SQL标准中实现的，因此不建议使用。 - marc_s

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- radar · Accepted Answer

如果类别的取值固定且在范围（1,7）内，则可以使用以下示例中的基于 case 的聚合。如果类别的取值是动态的，则需要使用动态 SQL 来进行数据透视表操作。

SELECT hist_statusevents.eqmt, 
       hist_exproot.shiftdate, 
       sum(case when hist_statusevents.category =1 then (hist_statusevents.duration/3600) else 0 end) as '1',
       sum(case when hist_statusevents.category =2 then (hist_statusevents.duration/3600) else 0 end) as '2',
       sum(case when hist_statusevents.category =3 then (hist_statusevents.duration/3600) else 0 end) as '3',
       sum(case when hist_statusevents.category =4 then (hist_statusevents.duration/3600) else 0 end) as '4',
       sum(case when hist_statusevents.category =5 then (hist_statusevents.duration/3600) else 0 end) as '5',
       sum(case when hist_statusevents.category =6 then (hist_statusevents.duration/3600) else 0 end) as '6',
       sum(case when hist_statusevents.category =7 then (hist_statusevents.duration/3600) else 0 end) as '7'

FROM Powerview.dbo.hist_eqmtlist hist_eqmtlist, Powerview.dbo.hist_exproot hist_exproot, Powerview.dbo.hist_statusevents hist_statusevents
WHERE hist_exproot.shiftindex = hist_statusevents.shiftindex And hist_statusevents.shiftindex = hist_eqmtlist.shiftindex And hist_statusevents.eqmt = hist_eqmtlist.eqmtid And hist_statusevents.eqmt like 'MOTO%'
GROUP BY hist_statusevents.eqmt, hist_exproot.shiftdate