How to combine multiple selects in one query

Databases
1

Hello,

I have one table and like to combine multiple select statements in one query.

tbl_students

ID Dept

1 A
2 B
3 A
4 C
5 B
6 D
7 E
8 F

SELECT Dept from tbl_students
where Dept in (‘A’, ‘B’, ‘C’)

SELECT Dept AS Dept2 from tbl_students
where Dept in (‘D’, ‘E’)

SELECT Dept AS Dept3 from tbl_students
where Dept in (‘F’)

How do I join these queries in one query to list the values in 3 columns as:

Dept1 Dept2 Dept3

A D F
B E
C

Thanks.

2

You could do something like


SELECT
    1 AS Deptnumber
  , Dept
FROM tbl_students
WHERE Dept IN ('A', 'B', 'C')
UNION
SELECT
    2 AS DeptNumber
  , Dept
FROM tbl_students
WHERE Dept IN ('D', 'E')
UNION
SELECT
    3 AS Deptnumber
  , Dept
FROM tbl_students
WHERE Dept IN ('F')

And then loop through the result set and elaborate the data according to the value of Deptnumber.

3

It worked perfect.

Thanks.

4

This is a great solution for combining queries to produce a single result, and very elegant! I will definitely be using this with frequency.

Here’s a question, though: suppose the number of sub-divisions of the query is indefinite, e.g. when the table contains a column that groups the rows based on a foreign key or some such. In this circumstance, the data to divide the results already exists in the table, but it can change as table data changes.

Is there a way to do a query like this dynamically, to do UNION / SELECT in a loop based on values in an existing column?

5

depends on which database you’re using

MS Access has CROSSTAB queries, which is the exact solution you want

MS SQL Server has PIVOT queries, but you have to specify all the columns

i think Oracle has PIVOT as well, but i’ve never used it

6

hhmmm… I use MySQL almost exclusively, mostly because of its ubiquitous nature. This could be bad news for me.

7

Never mind. A little digging has revealed I can do pivot tables in MySQL as well. Time to learn a new job skill.

8

o rly?

could you share the resource you found?

9

Cross-Tabulation (Pivot Tables) with MySQL – Cale Dunlap

Taken from:
MySQL :: MySQL Wizardry

10

that’s hardly wizardry

SELECT
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 60 AND CAST(gamedata.Data AS UNSIGNED) <= 87,1,0) ) AS '60-87',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 88 AND CAST(gamedata.Data AS UNSIGNED) <= 115,1,0) ) AS '88-115',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 116 AND CAST(gamedata.Data AS UNSIGNED) <= 143,1,0) ) AS '116-143',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 144 AND CAST(gamedata.Data AS UNSIGNED) <= 171,1,0) ) AS '144-171',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 172 AND CAST(gamedata.Data AS UNSIGNED) <= 199,1,0) ) AS '172-199',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 200 AND CAST(gamedata.Data AS UNSIGNED) <= 227,1,0) ) AS '200-227',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 228 AND CAST(gamedata.Data AS UNSIGNED) <= 255,1,0) ) AS '228-255',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 256 AND CAST(gamedata.Data AS UNSIGNED) <= 283,1,0) ) AS '256-283',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 284 AND CAST(gamedata.Data AS UNSIGNED) <= 311,1,0) ) AS '284-311',
    SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 312 AND CAST(gamedata.Data AS UNSIGNED) <= 339,1,0) ) AS '312-339'
FROM ...

that’s just brute force plain old SQL, even cruder than SQL Server’s PIVOT syntax

you have to hardcode for every column that you want

11

The first article was to solve a specific problem that author had; the second link gives a more detailed walkthrough of how to achieve cross-tabulation. I will still need to tinker with it to see if it can return data other than math calculations, because I would like to output VARCHAR fields that are a sorted list of categories and their subcategories. But it’s my understanding that SELECT CONCAT / SUM / IF will generate the SQL code necessary for the pivot table.