sql query change dimensional table

Question

i have a table : this is same with log for user login frequency

╔════════════╦═══════╦═════════════╗
║ month-year ║ user  ║ count_login ║
╠════════════╬═══════╬═════════════╣
║ 01-2016    ║ admin ║          12 ║
║ 01-2016    ║ user1 ║          10 ║
║ 02-2016    ║ admin ║           5 ║
║ 03-2016    ║ admin ║          15 ║
╚════════════╩═══════╩═════════════╝

its possiblly change to

╔════════════╦═══════╦═══════╗
║ month-year ║ admin ║ user1 ║
╠════════════╬═══════╬═══════╣
║ 01-2016    ║    12 ║    10 ║
║ 02-2016    ║     5 ║     0 ║
║ 03-2016    ║    15 ║     0 ║
╚════════════╩═══════╩═══════╝

if possible, what query can i use??

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

Use Conditional Aggregate to do this

select `month-year`, 
       sum(case when user = 'admin' then count_login else 0 end) as admin,
       sum(case when user = 'user1' then count_login else 0 end) as user1
From yourtable
Group by `month-year`

Check this answer for dynamic pivoting : MySQL pivot row into dynamic number of columns

edited May 23 '17 at 11:59

Community

1
1

answered Mar 16 '16 at 02:51

Pரதீப்

91,748
19
131
172

how if use a dynamical coloumn, that user may can be more than that user, may be user2, user3, ect. – Yanuar Ihsan Mar 16 '16 at 03:05
Updated my answer with link – Pரதீப் Mar 16 '16 at 03:08

sql query change dimensional table

1 Answers1