sql select display row data as columns

Question

I have a sql select query that extracts result as:

login_count  login_type
2000         iPhone
7000         browser

But i want the result as:

iphone_login  browser_login
2000          7000

i.e. i want to extract row1-col1 as col1 and row2-col2 as col2 using a select query.

My original query is

select count(login_count), login_type from log_table group by login_type;

Thanks, Gaurav

Abhijeet Ashok Muneshwar · Accepted Answer · 2017-03-02 08:49:59Z

3

Try this:

SELECT
    SUM( IF(login_type = 'iPhone', 1, 0) ) AS iphone_login,
    SUM( IF(login_type = 'browser', 1, 0) ) AS browser_login
FROM log_table

edited Mar 2, 2017 at 8:49

Abhijeet Ashok Muneshwar

2,5163 gold badges33 silver badges32 bronze badges

answered Feb 14, 2011 at 23:14

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan J Over a year ago

Incidentally, how many times have you given this answer? That isn't meant as a criticism, it just seems like I see at least one new "select rows as columns" question every single day. We need some kind of community-wide close-as-duplicate binge... If we could agree on which of these questions has the best 'canonical' answers... :)

Dan J Over a year ago

@Eray Then use the original query and let Excel transpose the types into columns for you. ;)

Ronnis · Accepted Answer · 2011-02-14 23:44:03Z

0

Here is another option that works in MySQL and in other dbms as well.

select sum(case when login_type = 'iPhone'  then 1 else 0 end) as iphone_login
      ,sum(case when login_type = 'browser' then 1 else 0 end) as browser_login
from log_table

answered Feb 14, 2011 at 23:44

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

sql select display row data as columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related