Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    4

    Unanswered: Select and rearrange / create column base on timestamp

    I have a data like below :

    Code:
    Timestamp,Services,Username,In,Out
    1405296000,ByServices,USER-A,-1,-1
    1405296300,ByServices,USER-A,-1,-1
    1405296600,ByServices,USER-A,-1,-1
    1405296900,ByServices,USER-A,-1,-1
    1405297200,ByServices,USER-A,-1,-1
    1405297500,ByServices,USER-A,-1,-1
    1405297800,ByServices,USER-A,-1,-1
    1405298100,ByServices,USER-A,-1,-1
    1405298400,ByServices,USER-A,-1,-1
    1405298700,ByServices,USER-A,-1,-1
    1405299000,ByServices,USER-A,-1,-1
    1405299300,ByServices,USER-A,-1,-1
    1405299600,ByServices,USER-A,-1,-1
    1405299900,ByServices,USER-A,-1,-1
    1405300200,ByServices,USER-A,-1,-1
    1405300500,ByServices,USER-A,-1,-1
    1405296000,ByServices,USER-B,1021215,44161
    1405296300,ByServices,USER-B,1029157,44577
    1405296600,ByServices,USER-B,1019812,43003
    1405296900,ByServices,USER-B,1028378,44788
    1405297200,ByServices,USER-B,1050908,45118
    1405297500,ByServices,USER-B,1031320,45941
    1405297800,ByServices,USER-B,1027745,46783
    1405298100,ByServices,USER-B,1029019,46127
    1405298400,ByServices,USER-B,1023649,44167
    1405298700,ByServices,USER-B,1052006,45444
    1405299000,ByServices,USER-B,114887,6666
    1405299300,ByServices,USER-B,995027,53362
    1405299600,ByServices,USER-B,1026964,52555
    1405299900,ByServices,USER-B,991064,46361
    1405300200,ByServices,USER-B,1041837,47942
    1405300500,ByServices,USER-B,1048323,47398
    1405296000,ByServices,USER-C,6873201,1449156
    1405296300,ByServices,USER-C,5170069,2525917
    1405296600,ByServices,USER-C,6180991,1637417
    1405296900,ByServices,USER-C,3717446,1873769
    1405297200,ByServices,USER-C,4922569,2093541
    1405297500,ByServices,USER-C,4793435,2230078
    1405297800,ByServices,USER-C,5528388,2241788
    1405298100,ByServices,USER-C,3352478,1065179
    1405298400,ByServices,USER-C,3074556,992567
    1405298700,ByServices,USER-C,2732403,727934
    1405299000,ByServices,USER-C,2302076,968986
    1405299300,ByServices,USER-C,3278972,274111
    1405299600,ByServices,USER-C,3667918,410107
    1405299900,ByServices,USER-C,2941738,340764
    1405300200,ByServices,USER-C,2965718,517362
    1405300500,ByServices,USER-C,2622347,659343
    1405296000,ByServices,USER-D,548915,25728
    1405296300,ByServices,USER-D,997594,30044
    1405296600,ByServices,USER-D,264138,26508
    1405296900,ByServices,USER-D,472043,25177
    1405297200,ByServices,USER-D,46523,6888
    1405297500,ByServices,USER-D,660933,40916
    1405297800,ByServices,USER-D,1550071,38069
    1405298100,ByServices,USER-D,1549335,39369
    1405298400,ByServices,USER-D,1553735,40086
    1405298700,ByServices,USER-D,1562226,38412
    1405299000,ByServices,USER-D,31460,6748
    1405299300,ByServices,USER-D,31302,5856
    1405299600,ByServices,USER-D,64211,6220
    1405299900,ByServices,USER-D,92698,6140
    1405300200,ByServices,USER-D,61425,6065
    1405300500,ByServices,USER-D,62419,4787
    1405296000,ByServices,USER-E,1149719,74808
    1405296300,ByServices,USER-E,2247826,111069
    1405296600,ByServices,USER-E,1913835,85707
    1405296900,ByServices,USER-E,596872,49864
    1405297200,ByServices,USER-E,584094,48658
    1405297500,ByServices,USER-E,1418039,79482
    1405297800,ByServices,USER-E,636519,47440
    1405298100,ByServices,USER-E,315834,21266
    1405298400,ByServices,USER-E,1162180,58062
    1405298700,ByServices,USER-E,699201,53893
    1405299000,ByServices,USER-E,142740,24015
    1405299300,ByServices,USER-E,1033022,51779
    1405299600,ByServices,USER-E,661104,42266
    1405299900,ByServices,USER-E,839492,54595
    1405300200,ByServices,USER-E,826536,59176
    1405300500,ByServices,USER-E,1265534,64839

    How do I query the data in one select statement so I will get result like this :

    Code:
    Timestamp ,UserA UserB         UserC           UserD        UserE
    1405296000,-1,-1,1021215,44161,6873201,1449156,548915,25728,1149719,74808
    1405296300,-1,-1,1029157,44577,5170069,2525917,997594,30044,2247826,111069
    1405296600,-1,-1,1019812,43003,6180991,1637417,264138,26508,1913835,85707
    1405296900,-1,-1,1028378,44788,3717446,1873769,472043,25177,596872,49864
    1405297200,-1,-1,1050908,45118,4922569,2093541,46523,6888,584094,48658
    1405297500,-1,-1,1031320,45941,4793435,2230078,660933,40916,1418039,79482
    1405297800,-1,-1,1027745,46783,5528388,2241788,1550071,38069,636519,47440
    1405298100,-1,-1,1029019,46127,3352478,1065179,1549335,39369,315834,21266
    1405298400,-1,-1,1023649,44167,3074556,992567,1553735,40086,1162180,58062
    1405298700,-1,-1,1052006,45444,2732403,727934,1562226,38412,699201,53893
    1405299000,-1,-1,114887,6666,2302076,968986,31460,6748,142740,24015
    1405299300,-1,-1,995027,53362,3278972,274111,31302,5856,1033022,51779
    1405299600,-1,-1,1026964,52555,3667918,410107,64211,6220,661104,42266
    1405299900,-1,-1,991064,46361,2941738,340764,92698,6140,839492,54595
    1405300200,-1,-1,1041837,47942,2965718,517362,61425,6065,826536,59176
    1405300500,-1,-1,1048323,47398,2622347,659343,62419,4787,1265534,64839
    I know how to split the first data to separate tables base on username and then query and join the tables . I just want to know is it possible to query the first data in one select statement ?

    Thanks in advance.
    Last edited by optimuscream; 07-15-14 at 06:27.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Somethin like...

    Code:
    SELECT Timestamp
         , MAX( CASE Username
                WHEN 'USER-A' THEN In
                END  ) AS UserA_In
         , MAX( CASE Username
                WHEN 'USER-A' THEN Out
                END  ) AS UserA_Out
         , MAX( CASE Username
                WHEN 'USER-B' THEN In
                END  ) AS UserB_In
         , MAX( CASE Username
                WHEN 'USER-B' THEN Out
                END  ) AS UserB_Out
    ...
    ...
         , MAX( CASE Username
                WHEN 'USER-E' THEN In
                END  ) AS UserE_In
         , MAX( CASE Username
                WHEN 'USER-E' THEN Out
                END  ) AS UserE_Out
     FROM  sample_data
     GROUP BY
           Timestamp
    ;

  3. #3
    Join Date
    Jun 2014
    Posts
    4
    Quote Originally Posted by tonkuma View Post
    Somethin like...

    Code:
    SELECT Timestamp
         , MAX( CASE Username
                WHEN 'USER-A' THEN In
                END  ) AS UserA_In
         , MAX( CASE Username
                WHEN 'USER-A' THEN Out
                END  ) AS UserA_Out
         , MAX( CASE Username
                WHEN 'USER-B' THEN In
                END  ) AS UserB_In
         , MAX( CASE Username
                WHEN 'USER-B' THEN Out
                END  ) AS UserB_Out
    ...
    ...
         , MAX( CASE Username
                WHEN 'USER-E' THEN In
                END  ) AS UserE_In
         , MAX( CASE Username
                WHEN 'USER-E' THEN Out
                END  ) AS UserE_Out
     FROM  sample_data
     GROUP BY
           Timestamp
    ;
    Thanks a lot ! Works .
    Again thanks.
    Last edited by optimuscream; 07-16-14 at 00:40. Reason: Found the reference

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
  •