2

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

The PIVOT operator may be of help. Take a look at Books Online

For example:

SELECT 
    DtStart AS Date, 
    ISNULL([1], 0) AS [Server 1], 
    ISNULL([2], 0) AS [Server 2], 
    ISNULL([3], 0) AS [Server 3]
FROM
(
    SELECT 
        DtStart, 
        nServerID, 
        NDirection 
    FROM dbo.events 
) AS SourceTable
PIVOT
(
    SUM(NDirection)
    FOR nServerID IN ([1], [2], [3])
) AS PivotTable
ORDER BY 
    DtStart ASC
  • made my day ; ) – Erdinc Ay Aug 12 '13 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.