How do I make the rows of a lookup table into the columns of a query?

Question

I have three tables: students, interests, and interest_lookup.

Students has the cols student_id and name.

Interests has the cols interest_id and interest_name.

Interest_lookup has the cols student_id and interest_id.

To find out what interests a student has I do

select interests.interest_name from `students`
  inner join `interest_lookup`
    on interest_lookup.student_id = students.student_id
  inner join `interests`
    on interests.interest_id = interest_lookup.interest_id

What I want to do is get a result set like

student_id | students.name | interest_a | interest_b | ...

where the column name 'interest_a' is a value in interests.name and the interest_ columns are 0 or 1 such that the value is 1 when there is a record in interest_lookup for the given student_id and interest_id and 0 when there is not.

Each entry in the interests table must appear as a column name.

I can do this with subselects (which is super slow) or by making a bunch of joins, but both of these really require that I first select all the records from interests and write out a dynamic query.

The only way I can imagine is to use pivotal technique, but it also requires knowing all values of `interest_name` in advance — a1ex07, Oct 05 '11 at 22:53

score 3 · Accepted Answer · answered Oct 05 '11 at 23:03

You're doing an operation called a pivot. @Slider345 linked to (prior to editing his answer) another SO post about doing it in Microsoft SQL Server. Microsoft has its own special syntax to do this, but MySQL does not.

You can do something like this:

SELECT s.student_id, s.name,
  SUM(i.name = 'a') AS interest_a,
  SUM(i.name = 'b') AS interest_b,
  SUM(i.name = 'c') AS interest_c
FROM students s
INNER JOIN interest_lookup l USING (student_id)
INNER JOIN interests i USING (interest_id)
GROUP BY s.student_id;

What you cannot do, in MySQL or Microsoft or anything else, is automatically populate columns so that the presence of data expands the number of columns.

Columns of an SQL query must be fixed and hard-coded at the time you prepare the query.

If you don't know the list of interests at the time you code the query, or you need it to adapt to changing lists of interest, you'll have to fetch the interests as rows and post-process these rows in your application.

Awesome. I couldn't figure out how pivot really worked till your example. Thanks! I did have to pull out all the interests first and dynamically generate the query to get all the interests. — baudtack, Oct 06 '11 at 16:03
Yes, you need to write code either way. You can write code to generate a query dynamically based on the values in the database, or else you can fetch the raw data and then write code to iterate over the results. — Bill Karwin, Oct 06 '11 at 18:00

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

What your trying to do sounds like a pivot.

Most solutions seem to revolve around one of the following approaches:

Creating a dynamic query, as in Is there a way to pivot rows to columns in MySQL without using CASE?
Selecting all the attribute columns, as in How to pivot a MySQL entity-attribute-value schema
Or, identifying the columns and using either a CASE statement or a user defined function as in pivot in mysql queries

edited May 23 '17 at 12:27

Community

1
1

answered Oct 05 '11 at 22:52

Slider345

4,558
7
39
47

Doesn't Pivot require knowing the values of interest_name in advance? – baudtack Oct 05 '11 at 22:54
2

I don't recommend that artful software site, though I see it cited frequently. Many of their query solutions are totally wrong. – Bill Karwin Oct 05 '11 at 23:04
Yeah, the more I read that artful link, the more I began to feel that it was needlessly confusing as well. – Slider345 Oct 05 '11 at 23:05

score 0 · Answer 3 · answered Oct 05 '11 at 23:01

I don't think this is possible. Actually I think this is just a matter of data representatioin. I would try to use a component to display the data that would allow me to pivot the data (for instance, the same way you do on excel, open office's calc, etc).

To take it one step further, you should think again why you need this and probably try to solve it in the application not in the database.

I know this doesn't help much but it's the best I can think of :(

How do I make the rows of a lookup table into the columns of a query?

3 Answers3