Creating view for Mysql data presented in horizontal way

Question

Sorry for an unclear topic but i am not sure how to better describe my situation here

I have a table similar to this:

And i was asked to create a view based on the table with some requirements, and the out come should be something like:

(I hard code this table to show my expected result for the View)

I am quite new to Create View Function and i've tried to look for solution online but i can't find much useful information.

The CREATE VIEW itself is no issue, solve its SELECT and you're there! — jarlh, Dec 09 '19 at 09:20
Will it always be A, B, C and D - or will more device types show up later? — jarlh, Dec 09 '19 at 09:21
@K.Leung , this questions has an already answered here ! https://stackoverflow.com/questions/31287118/transform-vertical-table-to-horizontal-with-mysql — Rahul Kr Daman, Dec 09 '19 at 09:57
is there anyway to create a view that can show future new devices? — K.Leung, Dec 11 '19 at 01:57

score 0 · Accepted Answer · answered Dec 09 '19 at 12:23

The syntax for creating a view is really simple:

create view <view name> as
    <select query here>;

The question that you have asked is for a view that returns exactly five columns. You can handle this with an aggregation query:

create view v_name as
    select device_id,
           sum(case when device_type = 'A' then count else 0 end) as a,
           sum(case when device_type = 'B' then count else 0 end) as b,
           sum(case when device_type = 'C' then count else 0 end) as c,
           sum(case when device_type = 'D' then count else 0 end) as d
    from t  -- your table name here
    group by device_id;

The caveat is that this returns exactly the types that are in the SELECT -- no others if they are in the table. If you need flexible columns, then you have a problem. It is not hard to construct a dynamic query to return the values at any given time. However, you cannot put dynamic SQL in a view. If this is something you need, then some alternative mechanism -- such as JSON -- might be needed.

Thanks for the answer, that helps a lot! does that mean if there are more device that would need JSON to do so? — K.Leung, Dec 10 '19 at 02:12
@K.Leung . . . No. If you do not know the complete list, then JSON may be helpful. — Gordon Linoff, Dec 10 '19 at 04:18

Creating view for Mysql data presented in horizontal way

1 Answers1