How to pivot rows in a table with condition?

Question

I want to pivot data in my database with an SQL script. This is how it looks like in the table.

id  name        state   cpu_usage   memory_usage    datetime
1   machine-2   Running 50.6693     5060099372      2013-11-18 17:00:22.550
2   machine-3   Running 19.8329     14951967776     2013-11-18 17:00:22.550
3   machine-4   Running 24.2103     3309923047      2013-11-18 17:00:22.550
4   machine-2   Running 97.2774     2867303718      2013-11-18 17:01:22.550
5   machine-3   Running 66.2991     11048043786     2013-11-18 17:01:22.550
6   machine-5   Running 26.6446     13362336682     2013-11-18 17:01:22.550
7   machine-2   Running 39.5881     6969468931      2013-11-18 17:02:22.550
8   machine-3   Running 99.0000     9340168327      2013-11-18 17:02:22.550
9   machine-4   Running 85.0968     16154409604     2013-11-18 17:02:22.550

This is how I want the result to look like for CPU usage.

Date                        machine-2   machine-3   machine-4   machine-5
2013-11-18 17:00:22.550     50.6693     19.8329     24.2103     null
2013-11-18 17:01:22.550     97.2774     66.2991     null        26.6446
2013-11-18 17:02:22.550     39.5881     99.0000     85.0968     null

How to do this with SQL?

Answer 1

There are a few different ways that you can get the result.

You can apply an aggregate function with a CASE expression using conditional logic to create your new columns:

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;

See SQL Fiddle with Demo

Or you can apply the PIVOT function which will convert your name values into columns:

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;

See SQL Fiddle with Demo.

The above two versions will work for you if you have a limited number of values for the name columns, but if not then you will need to look at using dynamic 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;

See SQL Fiddle with Demo. All versions give a result:

|                        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) |