Difficulty with Pivot in sql

Question

I have two tables, title and territory. Territory tells me where the title is available:

`title`
- id
- name

`territory`
- title_id (FK)
- territory

I want the output to look like this:

id      name     territory     territory     territory     etc...
1       Titanic  US            GB            FR

I am currently doing an INNER JOIN, which gives me three rows for the above instead of one:

SELECT * FROM title inner join territory on title.id = territory.title_id

How would I get it in the above output, where athere is a single row per title, and all territories are listed in that row?

You need a pivot to do that, but unfortunately under mysql this is really hard to emulate. What about having all territories in a single selected column "territories"? — Sebas, May 10 '13 at 22:08
This is not what "outer join" means in SQL. The difference between inner and outer join is only in what happens to rows that have no match in the joining table. — Barmar, May 10 '13 at 22:21

score 2 · Accepted Answer · edited May 23 '17 at 11:49

A fast alternative solution could be to aggregate all the territories in a single column:

SELECT t.id, t.name, GROUP_CONCAT(tr.territory, ',') AS `territories`
    FROM title t
        JOIN territory tr ON t.id = tr.title_id
GROUP BY t.id, t.name

It is usually easy to split the result in your application afterwards. But if you really want a pivot you'll have to be tricky.

Regarding to your title, there's no need to use outer joins here unless you're not sure that a title has a territory affected. In that case, use a LEFT JOIN instead.

Difficulty with Pivot in sql

1 Answers1