Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Question Unanswered: Mysql show data in Pivot View

    Sorry Guys, could not generate a fiddle since facing some issues with sqlfiddle.com

    I have a table in mysql database which is as follows:

    Code:
    Username    mailtime    mailid
    User4   3/23/2013 10:26 4473
    User1   4/6/2013 16:13  10934
    User4   4/6/2013 17:17  10957
    User1   4/6/2013 23:56  11092
    User2   4/7/2013 11:58  11187
    User1   4/7/2013 12:06  11190
    User4   4/7/2013 13:11  11216
    User4   4/7/2013 13:14  11217
    User1   4/7/2013 14:40  11245
    User5   4/7/2013 15:52  11259
    User1   4/7/2013 18:12  11303
    User5   4/7/2013 19:56  11323
    User1   4/7/2013 22:52  11358
    User4   4/8/2013 11:13  11465
    User1   4/8/2013 11:20  11475
    User1   4/8/2013 11:35  11491
    User4   4/8/2013 12:10  11511
    User4   4/8/2013 12:38  11532
    User4   4/8/2013 12:51  11540
    User4   4/8/2013 13:06  11551
    User1   4/8/2013 13:09  11552
    User4   4/8/2013 13:15  11560
    User1   4/8/2013 13:24  11572
    User1   4/8/2013 14:01  11614
    User4   4/8/2013 14:27  11640
    User1   4/8/2013 15:41  11700
    User5   4/8/2013 16:04  11730
    User1   4/8/2013 17:40  11814
    User4   4/9/2013 11:16  12117
    User1   4/9/2013 12:41  12198
    User1   4/9/2013 12:59  12209
    User4   4/9/2013 13:58  12243
    User4   4/9/2013 14:05  12250
    User1   4/9/2013 14:15  12256
    User4   4/9/2013 16:51  12351
    User1   4/9/2013 17:33  12397
    User1   4/9/2013 19:01  12455
    User4   4/9/2013 19:15  12463
    User5   4/9/2013 20:59  12517
    User1   4/9/2013 21:26  12530
    User1   4/9/2013 22:46  12561
    User1   4/10/2013 1:01  12595
    User1   4/10/2013 8:42  12631
    User1   4/10/2013 10:18 12663
    User1   4/10/2013 11:21 12697
    User3   4/10/2013 11:27 12701
    User4   4/10/2013 11:34 12705
    User1   4/10/2013 15:26 12856
    User4   4/10/2013 16:51 12909
    User2   4/10/2013 16:53 12913
    The output that i require is as follows:

    Code:
    Username    < 5 days    6-Apr   7-Apr   8-Apr   9-Apr   10-Apr  Grand Total
    User1                           
    User2                           
    User3                           
    User4                           
    User5                           
    Grand Total
    Not sure how to do this.

    The data that is required is the count of mailids

    The columns < 5 days, 6-Apr, 7-Apr, 8-Apr, 9-Apr and 10-Apr are the columns from mailtime Column. If the max date from the column is say 25th Mar, the the columns should be < 5 days, 20-Mar, 21-Mar, 22-Mar, 23-Mar, 24 Mar and 25-Mar

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is possible but very complex and would be easier to do it in a program than in an SQL statement.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Ronan.

    But, my curiosity whispered to me something other than the common sense.

    Here is an example worked on DB2 9.7.5 on Windows.
    You may want to modify it to meet with the syntax of MySQL.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     max_date( max_maildate ) AS (
    SELECT DATE( MAX(mailtime) )
     FROM  gunjan.sample_data
    )
    SELECT 'User Name'   AS " "
         , '< 5 days'    AS "  "
         , CHAR( INITCAP( TO_CHAR(max_maildate - 4 days , 'dd-mon') ) , 6 ) AS "  "
         , CHAR( INITCAP( TO_CHAR(max_maildate - 3 days , 'dd-mon') ) , 6 ) AS "   "
         , CHAR( INITCAP( TO_CHAR(max_maildate - 2 days , 'dd-mon') ) , 6 ) AS "    "
         , CHAR( INITCAP( TO_CHAR(max_maildate - 1 days , 'dd-mon') ) , 6 ) AS "     "
         , CHAR( INITCAP( TO_CHAR(max_maildate          , 'dd-mon') ) , 6 ) AS "      "
         , 'Grand total' AS "       " 
         , -1            AS "        "
     FROM  max_date
    UNION ALL
    SELECT COALESCE(username , 'Grand total')
         , CHAR( COUNT( CASE WHEN DATE(mailtime) < max_maildate - 4 days THEN 0 END ) )
         , CHAR( COUNT( CASE WHEN DATE(mailtime) = max_maildate - 4 days THEN 0 END ) )
         , CHAR( COUNT( CASE WHEN DATE(mailtime) = max_maildate - 3 days THEN 0 END ) )
         , CHAR( COUNT( CASE WHEN DATE(mailtime) = max_maildate - 2 days THEN 0 END ) )
         , CHAR( COUNT( CASE WHEN DATE(mailtime) = max_maildate - 1 days THEN 0 END ) )
         , CHAR( COUNT( CASE WHEN DATE(mailtime) = max_maildate          THEN 0 END ) )
         , CHAR( COUNT(mailtime) )
         , GROUPING(username)
     FROM  max_date           AS m
     CROSS JOIN
           gunjan.sample_data AS s
     GROUP BY
           ROLLUP( username )
     ORDER BY
           "        " /* or 9 */
         , 1
    ;
    ------------------------------------------------------------------------------
    
                                                                                                               
    ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    User Name   < 5 days    06-Apr      07-Apr      08-Apr      09-Apr      10-Apr      Grand total          -1
    User1       0           2           4           7           7           5           25                    0
    User2       0           0           1           0           0           1           2                     0
    User3       0           0           0           0           0           1           1                     0
    User4       1           1           2           7           5           2           18                    0
    User5       0           0           2           1           1           0           4                     0
    Grand total 1           3           9           15          13          9           50                    1
    
      7 record(s) selected.
    CREATE TABLE statement and INSERT statement to populate the table.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE gunjan.sample_data
    ( username VARCHAR(10) NOT NULL
    , mailtime TIMESTAMP   NOT NULL
    , mailid   INTEGER     NOT NULL
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO gunjan.sample_data
    SELECT username
         , TO_DATE(mailtime , 'mm/dd/yyyy hh24:mi')
         , mailid
     FROM (
    VALUES
      ( 'User4' , '3/23/2013 10:26' ,  4473 )
    , ( 'User1' , '4/06/2013 16:13' , 10934 )
    , ( 'User4' , '4/06/2013 17:17' , 10957 )
    , ( 'User1' , '4/06/2013 23:56' , 11092 )
    , ( 'User2' , '4/07/2013 11:58' , 11187 )
    , ( 'User1' , '4/07/2013 12:06' , 11190 )
    , ( 'User4' , '4/07/2013 13:11' , 11216 )
    , ( 'User4' , '4/07/2013 13:14' , 11217 )
    , ( 'User1' , '4/07/2013 14:40' , 11245 )
    , ( 'User5' , '4/07/2013 15:52' , 11259 )
    , ( 'User1' , '4/07/2013 18:12' , 11303 )
    , ( 'User5' , '4/07/2013 19:56' , 11323 )
    , ( 'User1' , '4/07/2013 22:52' , 11358 )
    , ( 'User4' , '4/08/2013 11:13' , 11465 )
    , ( 'User1' , '4/08/2013 11:20' , 11475 )
    , ( 'User1' , '4/08/2013 11:35' , 11491 )
    , ( 'User4' , '4/08/2013 12:10' , 11511 )
    , ( 'User4' , '4/08/2013 12:38' , 11532 )
    , ( 'User4' , '4/08/2013 12:51' , 11540 )
    , ( 'User4' , '4/08/2013 13:06' , 11551 )
    , ( 'User1' , '4/08/2013 13:09' , 11552 )
    , ( 'User4' , '4/08/2013 13:15' , 11560 )
    , ( 'User1' , '4/08/2013 13:24' , 11572 )
    , ( 'User1' , '4/08/2013 14:01' , 11614 )
    , ( 'User4' , '4/08/2013 14:27' , 11640 )
    , ( 'User1' , '4/08/2013 15:41' , 11700 )
    , ( 'User5' , '4/08/2013 16:04' , 11730 )
    , ( 'User1' , '4/08/2013 17:40' , 11814 )
    , ( 'User4' , '4/09/2013 11:16' , 12117 )
    , ( 'User1' , '4/09/2013 12:41' , 12198 )
    , ( 'User1' , '4/09/2013 12:59' , 12209 )
    , ( 'User4' , '4/09/2013 13:58' , 12243 )
    , ( 'User4' , '4/09/2013 14:05' , 12250 )
    , ( 'User1' , '4/09/2013 14:15' , 12256 )
    , ( 'User4' , '4/09/2013 16:51' , 12351 )
    , ( 'User1' , '4/09/2013 17:33' , 12397 )
    , ( 'User1' , '4/09/2013 19:01' , 12455 )
    , ( 'User4' , '4/09/2013 19:15' , 12463 )
    , ( 'User5' , '4/09/2013 20:59' , 12517 )
    , ( 'User1' , '4/09/2013 21:26' , 12530 )
    , ( 'User1' , '4/09/2013 22:46' , 12561 )
    , ( 'User1' , '4/10/2013 01:01' , 12595 )
    , ( 'User1' , '4/10/2013 08:42' , 12631 )
    , ( 'User1' , '4/10/2013 10:18' , 12663 )
    , ( 'User1' , '4/10/2013 11:21' , 12697 )
    , ( 'User3' , '4/10/2013 11:27' , 12701 )
    , ( 'User4' , '4/10/2013 11:34' , 12705 )
    , ( 'User1' , '4/10/2013 15:26' , 12856 )
    , ( 'User4' , '4/10/2013 16:51' , 12909 )
    , ( 'User2' , '4/10/2013 16:53' , 12913 )
    ) AS t( username , mailtime , mailid );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Last edited by tonkuma; 04-21-13 at 23:44.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •