sql query for data format

Question

This is my query for data is like:

select 
    ('moviestimeidea') as 'circle',
    count(distinct msisdn),
    vendor,
    hour(proess_datetime) 
from 
    campaing_info 
where 
    date(proess_datetime) = curdate() 
    and length(msisdn) > 4 
group by 
    vendor,
    hour(proess_datetime) 
order by 
    hour(proess_datetime);

Which returns this result:

circle            count(distinct ani)    vendor    hour(date_time) 
------------------------------------------------------------------
xyz               1                      default   0
xyx               5                      a         0
xyx               5                      b         1
xyx               27                     c         1
xyx               5                      a         3
xyx               54                     b         3
xyx               57                     c         4
xyx               5                      d         5

but I want this result instead:

 circle            vendors            hour(date_time) 
 ----------------------------------------------------
 -            default a   b c d          -
xyx               1   -   - - -          0
xyx               -   5   - - -          0
xyx               -   -   5 - -          1
xyx               -   -   - - -          1
xyx               -  54   - - -          3
xyx               -   -  54 - -          3
xyx               -   -   - - -          4
xyx               -   -   - - -          5

like data for c and d vendor Please help

It seems that you are trying to [pivot](https://stackoverflow.com/questions/7674786/mysql-pivot-table) your data — litelite, Aug 31 '17 at 19:45
What makes you think that SQL results can have two header rows? — Tab Alleman, Aug 31 '17 at 20:19
`as 'circle'` is invalid standard SQL. Which DBMS are you using? — , Aug 31 '17 at 20:36
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Ian Kenney, Sep 01 '17 at 07:26

score 0 · Answer 1 · edited Oct 30 '17 at 09:14

select
    hour(date_add(tbl.date_time,interval 335 minute)) as 'hour'
    ,count(distinct tbl1.ani) as 'XYZ' 
    ,count(distinct tbl2.ani) as 'ASDF' 
    ,count(distinct tbl3.ani) as 'FGH'
    ,count(distinct tbl4.ani) as 'HJK'
    ,count(distinct tbl5.ani) as 'KLO'
    from TABLE NAME tbl
    left outer join TABLE NAME tbl1 on tbl.id=tbl1.id  and tbl1.vendor='XYZ'
    left outer join TABLE NAME tbl2 on tbl.id=tbl2.id  and tbl2.vendor='ASDF'
    left outer join TABLE NAMEl tbl3 on tbl.id=tbl3.id  and tbl3.vendor='FGH'
    left outer join TABLE NAME tbl4 on tbl.id=tbl4.id  and tbl4.vendor='HJK'
    left outer join TABLE NAME tbl5 on tbl.id=tbl5.id  and tbl5.vendor='KLO'
    Where date(tbl.date_time)=date(date_add(now(),interval 335 minute)) group by hour(date_add(tbl.date_time,interval 335 minute));

sql query for data format

1 Answers1