Question 286612 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 2 separate results displayed in columns

I’m a my SQL beginner, I need to know how to display the results from 3 queries that are different in row-count. They’ll be querying the same table. Example table:

Scores
NAME    | SCORE
jack    |  1 
mike    |  3
kate    |  2
able    |  1
jill    |  2
seth    |  1

the 3 single select statements look like this:

SELECT name as Ones
FROM scores
WHERE score = 1

SELECT name as Twos
FROM scores
WHERE score = 2

SELECT name as Threes
FROM scores
WHERE score = 3

Separately the results to each query would give different row counts, but I want them in columns as below:

Ones     | Twos      | Threes
______________________________
jack     | mike      | kate 
able     | jill      |
seth     |           |

Whether there’s a blank space or a null in the gaps I wouldn’t mind.

Tags:

2

Answers


  1. 0

    As you can accept even empty spaces or nulls, you can use UNION ALL like this:

    SELECT [name] as Ones, '' as Twos, '' as Threes FROM scores WHERE score = 1
union all
SELECT '' as Ones, [name] as Twos, '' as Threes FROM scores WHERE score = 2
union all
SELECT '' as Ones, '' as Twos, [name] as Threes FROM scores WHERE score = 3

    to obtain what you asked for.
    You can only use union all with tables with the same structure, in this case 3 columns each with the same names.

    Final result is

    Ones            Twos            Threes
------------    ------------    ------------
jack                                                                                                  
able                                                                                                  
seth                                                                                                  
                kate                                               
                jill                                               
                                mike
    Login or Signup to reply.
  2. 0

    Use row_number() for each score class.

    Have cnt derived table that returns the total number of rows.

    LEFT JOIN the score classes.

    select s1.ones, s2.twos, s3.threes
from
(select row_number() over () rn FROM scores) cnt
left join
  (SELECT name as ones, row_number() over () rn FROM scores WHERE score = 1) s1
  on cnt.rn = s1.rn
left outer join
(SELECT name as twos, row_number() over () rn FROM scores WHERE score = 2) s2
  on cnt.rn = s2.rn
left outer join
(SELECT name as threes, row_number() over () rn FROM scores WHERE score = 3) s3
  on cnt.rn = s3.rn
where s1.rn is not null or s2.rn is not null or s3.rn is not null

    Demo: https://dbfiddle.uk/CQiMcs6v

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search