Home MariaDB SQL: Dynamic Columns rather than Rows For Multiple Results Against a Single source

Questions

MariaDB SQL: Dynamic Columns rather than Rows For Multiple Results Against a Single source

March 16, 2022

I have a list of contacts with numerous sectors they work in, how many sectors a contact has varies and it’s possible they have 0.
When I run the query rather than creating duplications of the contact to accommodate these multiple sectors, is it possible add additional columns should more than one result be found?

My Results now:

  email            sector
1 bob@work.com     builder
2 bob@work.com     construction
3 sally@work.com   NULL
4 greg@email.com   builder
5 jane@hello.com   baker
6 peter@hi.com     painter
7 peter@hi.com     finance
8 peter@hi.com     money-management

Desired Outcome:

  email            sector       sector2        sector3
1 bob@work.com     builder      construction   NULL
3 sally@work.com   NULL         NULL           NULL
4 greg@email.com   builder      NULL           NULL
5 jane@hello.com   baker        NULL           NULL
6 peter@hi.com     painter      finance        money-management

>Solution :

Assuming you want to report only 3 sectors, we can try a pivot query with the help of ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY sector) rn
    FROM yourTable
)

SELECT
    email,
    MAX(CASE WHEN rn = 1 THEN sector END) AS sector,
    MAX(CASE WHEN rn = 2 THEN sector END) AS sector2,
    MAX(CASE WHEN rn = 3 THEN sector END) AS sector3
FROM cte
GROUP BY email;