CODEWITHSUNDEEP
mysqlsqlpivot
David542
David542

Reputation: 110492

Difficulty with Pivot in sql

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?

Upvotes: 1

Views: 72

Answers (1)

Sebas
Sebas

Reputation: 21542

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.

Upvotes: 2

Related Questions