Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
share|improve this question

1 Answer 1

up vote 5 down vote accepted

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
share|improve this answer
    
made my day ; ) –  Erdinc Ay Aug 12 '13 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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