How to convert SQL server result set Rows to Column with variable Comumn and rows

Question

I am getting this data from into one of view, which internally collect from various sources, I would like to format this data in normalised way, so this can be easily used in SSRS for charting purpose.

Raw Data

ServerName  TimeStamp               CPU_PCT_Utilization
Server_01   2015-04-16 16:23:04.000 2.781995773
Server_01   2015-04-16 16:28:04.000 2.804701567
Server_01   2015-04-16 17:23:04.000 2.804701567
Server_02   2015-04-21 04:33:02.000 1.094034672
Server_02   2015-04-20 17:28:02.000 1.34284699
Server_02   2015-04-20 17:33:02.000 3.027540922
Server_02   2015-04-20 13:28:03.000 0.860414088
Server_02   2015-04-20 13:33:03.000 0.785780609
Server_02   2015-04-20 18:28:02.000 3.027540922
Server_03   2015-04-21 07:04:57.000 1.316953659
Server_03   2015-04-21 07:09:57.000 1.483076811
Server _??  …   .
Server _??  ..  .
Server _??  .   .
Server _??  .   .

Required OUTPUT

TimeStamp   Server_01   Server_02   Server_03   Server _??  Server _??
2015-04-16 16:23:04.000 2.781995773 NULL    NULL    NULL    .
2015-04-16 16:28:04.000 2.804701567 NULL    NULL    .   …
2015-04-16 17:23:04.000 2.804701567 NULL    NULL    …   .
2015-04-21 04:33:02.000 NULL    1.094034672 NULL    .   .
2015-04-20 17:28:02.000 NULL    1.34284699  NULL    .   …
2015-04-20 17:33:02.000 NULL    3.027540922 NULL    …   NULL
2015-04-20 13:28:03.000 NULL    0.860414088 NULL    .   NULL
2015-04-20 13:33:03.000 NULL    0.785780609 NULL    .   NULL
2015-04-20 18:28:02.000 NULL    3.027540922 NULL    …   NULL
2015-04-21 07:04:57.000 NULL    NULL    1.316953659 .   ..
2015-04-21 07:09:57.000 NULL    NULL    1.483076811 .   .
…   .   …   .   NULL    …
..  .   ..  .   .   …
.   …   NULL    …   .   .
.   .   .   .   …   .

I tried using pivot but that didn't helped, if anyone can write a query for this will be great, here rows and columns both are dynamic.

A similar solution is posted at Simple way to transpose columns and rows in Sql? but that with fixed values.

void · Accepted Answer · 2015-05-21T19:15:17.723

The values to be rotated to columns are not constant, you have to use Dynamic SQL, you need dynamic sql just to build below statement:

case ServerName
   when 'Server_n' then CPU_PCT_Utilization
   else null 
end as Server_n

for building above statement you can select distinct server names in above template into a variable:

declare @CaseStmnt varchar(max)=''
select @CaseStmnt=',case ServerName
when '''+s+''' then CPU_PCT_Utilization
else null 
end as '+s+@CaseStmnt
from (select distinct ServerName s from pvtTbl) q

Below is a demo of what I said:

MS SQL Server Schema Setup:

create table pvtTbl(ServerName varchar(10), dt datetime,CPU_PCT_Utilization float);
go
insert into pvtTbl values
('Server_01',   '2015-04-16 16:23:04.000', 2.781995773),
('Server_01',   '2015-04-16 16:28:04.000', 2.804701567),
('Server_01',   '2015-04-16 17:23:04.000', 2.804701567),
('Server_02',   '2015-04-21 04:33:02.000', 1.094034672),
('Server_02',   '2015-04-20 17:28:02.000', 1.34284699),
('Server_02',   '2015-04-20 17:33:02.000', 3.027540922),
('Server_02',   '2015-04-20 13:28:03.000', 0.860414088),
('Server_02',   '2015-04-20 13:33:03.000', 0.785780609),
('Server_02',   '2015-04-20 18:28:02.000', 3.027540922),
('Server_03',   '2015-04-21 07:04:57.000', 1.316953659),
('Server_03',   '2015-04-21 07:09:57.000', 1.483076811);

Query:

declare @CaseStmnt varchar(max)=''
select @CaseStmnt=@CaseStmnt+', case ServerName
when '''+s+''' then CPU_PCT_Utilization
else null 
end as '+s
from (select distinct ServerName s from pvtTbl) q 
exec('select dt '+@CaseStmnt+' from pvtTbl')

Results:

|                      dt |   Server_01 |   Server_02 |   Server_03 |
|-------------------------|-------------|-------------|-------------|
| April, 16 2015 16:23:04 | 2.781995773 |      (null) |      (null) |
| April, 16 2015 16:28:04 | 2.804701567 |      (null) |      (null) |
| April, 16 2015 17:23:04 | 2.804701567 |      (null) |      (null) |
| April, 21 2015 04:33:02 |      (null) | 1.094034672 |      (null) |
| April, 20 2015 17:28:02 |      (null) |  1.34284699 |      (null) |
| April, 20 2015 17:33:02 |      (null) | 3.027540922 |      (null) |
| April, 20 2015 13:28:03 |      (null) | 0.860414088 |      (null) |
| April, 20 2015 13:33:03 |      (null) | 0.785780609 |      (null) |
| April, 20 2015 18:28:02 |      (null) | 3.027540922 |      (null) |
| April, 21 2015 07:04:57 |      (null) |      (null) | 1.316953659 |
| April, 21 2015 07:09:57 |      (null) |      (null) | 1.483076811 |

score 0 · Answer 2 · answered May 21 '15 at 21:14

You could create a variable @cols and use STUFF() function with XML path to build a dynamic list of serverName to pass in the pivot list operator like this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ServerName)  
            FROM pvtTbl c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT dt, ' + @cols + ' from 
            (
                select serverName,dt,CPU_PCT_Utilization
                from pvtTbl
           ) x
            pivot 
            (
                 max(CPU_PCT_Utilization)
                for ServerName in (' + @cols + ')
            ) p '


execute(@query)

Please refer to the SQL Fiddle Solution below:

SQL Fiddle Dynamic Pivot Example

How to convert SQL server result set Rows to Column with variable Comumn and rows

2 Answers2