I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise.
The two dimensions are following:
- Datetime values (per Day)
- ServerId (per Server)
I think I have to group Datetime values by a time part (e.g. by minutes, or per hour) and retrieve a list of occourences of events per server, but I dont know how to write the query? Do I need a having by clause?
I've already written the create statements, the structure is as following:
CREATE TABLE [dbo].[events](
[guidEventId] [uniqueidentifier] PRIMARY KEY,
[dtStart] [datetime2](7) NOT NULL,
[nDirection] [int] NOT NULL,
[nServerId] [int] NOT NULL
)
INSERT INTO events
([guidEventId],[dtStart],[nDirection],[nServerId])
VALUES
(newId(),'2013-08-01 00:01:00','0','1'),
(newId(),'2013-08-01 00:02:00','1','1'),
(newId(),'2013-08-01 00:03:00','0','1'),
(newId(),'2013-08-01 00:04:00','1','2'),
(newId(),'2013-08-02 00:01:00','0','1'),
(newId(),'2013-08-02 00:02:00','1','2'),
(newId(),'2013-08-02 00:03:00','0','2'),
(newId(),'2013-08-02 00:04:00','1','2');
http://sqlfiddle.com/#!6/25e74
The end table should look something like that (daywise):
DateTime | Server1 | Server2 | Sum |
--------------------------------------------
2013-08-01 | 3 | 1 | 4 |
2013-08-02 | 1 | 3 | 4 |
Best Answer
The PIVOT operator may be of help. Take a look at Books Online
For example: