有几种不同的方法可以得到结果。
您可以使用条件逻辑将具有CASE表达式的聚合函数应用于创建新列：
select datetime,
max(case when name = 'machine-2' then cpu_usage end) machine2,
max(case when name = 'machine-3' then cpu_usage end) machine3,
max(case when name = 'machine-4' then cpu_usage end) machine4,
max(case when name = 'machine-5' then cpu_usage end) machine5
from yourtable
group by datetime;
或者，您可以应用PIVOT函数，该函数会将您的name值转换为列：
select datetime,
[machine-2], [machine-3],
[machine-4], [machine-5]
from
(
select datetime, name, cpu_usage
from yourtable
) d
pivot
(
max(cpu_usage)
for name in ([machine-2], [machine-3], [machine-4], [machine-5])
) piv;
请参阅带有演示的SQL Fiddle。
如果name列的值数量有限，则以上两个版本将为您工作，但是如果没有，那么您将需要使用动态sql：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT datetime,' + @cols + '
from
(
select datetime, name, cpu_usage
from yourtable
) x
pivot
(
max(cpu_usage)
for name in (' + @cols + ')
) p '
execute sp_executesql @query;
请参阅带有演示的SQL Fiddle。所有版本都会产生结果：
| DATETIME | MACHINE-2 | MACHINE-3 | MACHINE-4 | MACHINE-5 |
|---------------------------------|-----------|-----------|-----------|-----------|
| November, 18 2013 17:00:22+0000 | 50.6693 | 19.8329 | 24.2103 | (null) |
| November, 18 2013 17:01:22+0000 | 97.2774 | 66.2991 | (null) | 26.6446 |
| November, 18 2013 17:02:22+0000 | 39.5881 | 99 | 85.0968 | (null) |
602392714
清零编程群