[SQL] How to "pivot" events by day?

Posted on 2006-05-08
Last Modified: 2012-05-05
Hello, everybody

I have the following problem. I have a database table that contains "events", that represents transitions of users in and out from a dedicated reader.

The table is like this (simplified):

ID      Unique ID (primary key, autoincrement)
Date    Date and time of the event
Code    Numeric code of the event (indicating if this is an in or an out from the reader)

What I need to do is this: I want to "group" the transition by day, indicating all the transitions of a day in the same row.
For instance:

                Tr_I    Tr_II   Tr_III  Tr_IV
05/08/06        06:15   12:35   14:00   17:40
05/09/06        07:15   11:35  
05/10/06        06:35   12:25   13:00   16:40

Here is the question: is this possible via a SQL statement?
(I'm using SQLite, so I can't use stored procedures).

Due to the kind of the application, I can assume the maximum number of transitions per day (4, in the example above) is fixed.

Thanks for all the replies,

Question by:Hit_MN
    LVL 42

    Accepted Solution

    I don't know SQLite, but here is the general form that should be pretty close:

    Select dateonly as date, t1.timeonly as time1 , t2.timeonly as time2, t3.timeonly as time3, t4.timeonly as time4
    from (((yourtable as T1
    left join yourtable as T2 on T1.dateonly = t2.dateonly and T2.datetime > T1.Datetime)
    left join yourtable as T3 on T2.dateonly = t3.dateonly and T3.datetime > T2.Datetime)
    left join yourtable as T4 on T3.dateonly = t4.dateonly and T4.datetime > T4.Datetime)

    You will need to figure for your SQL dialect how to get dateonly and timeonly out of your datetime column.


    Expert Comment


    You want to cross tab rpeort
    please check below article found in sql server help  , It  will give a definate idea to solve ur ?problem

    Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data.

    Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

    Year      Quarter      Amount
    ----      -------      ------
    1990      1           1.1
    1990      2           1.2
    1990      3           1.3
    1990      4           1.4
    1991      1           2.1
    1991      2           2.2
    1991      3           2.3
    1991      4           2.4

    A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:


    These are the statements used to create the Pivot table and populate it with the data from the first table:

    USE Northwind

    ( Year      SMALLINT,
      Quarter   TINYINT,
      Amount      DECIMAL(2,1) )
    INSERT INTO Pivot VALUES (1990, 1, 1.1)
    INSERT INTO Pivot VALUES (1990, 2, 1.2)
    INSERT INTO Pivot VALUES (1990, 3, 1.3)
    INSERT INTO Pivot VALUES (1990, 4, 1.4)
    INSERT INTO Pivot VALUES (1991, 1, 2.1)
    INSERT INTO Pivot VALUES (1991, 2, 2.2)
    INSERT INTO Pivot VALUES (1991, 3, 2.3)
    INSERT INTO Pivot VALUES (1991, 4, 2.4)

    This is the SELECT statement used to create the rotated results:

    SELECT Year,
        SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
        SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
        SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
        SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
    FROM Northwind.dbo.Pivot
    GROUP BY Year

    This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

    If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:

    SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
    FROM (SELECT Year,
                 SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
                 SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
                 SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
                 SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
         FROM Pivot AS P
         GROUP BY P.Year) AS P1

    Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.

    LVL 6

    Author Comment

    Sorry for the delay replying:

    dqmq: I think that your solution is the one to go... even if it shall be quite complicated in the "real world", since I have different "events" to indicate entrance and exit, so I should filter differently tables depending on that.
    Please, let me some days to test this, and I'll come back to you...

    amarpowar: I think that your solution is related more to "pivotitng" the table, which is not exacly what I need (I do not need to sum values)... maybe the title was confusing... Thanks anyhow.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    This article describes some very basic things about SQL Server filegroups.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now