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

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 [email protected]     builder
2 [email protected]     construction
3 [email protected]   NULL
4 [email protected]   builder
5 [email protected]   baker
6 [email protected]     painter
7 [email protected]     finance
8 [email protected]     money-management

Desired Outcome:

  email            sector       sector2        sector3
1 [email protected]     builder      construction   NULL
3 [email protected]   NULL         NULL           NULL
4 [email protected]   builder      NULL           NULL
5 [email protected]   baker        NULL           NULL
6 [email protected]     painter      finance        money-management

CodePudding user response：

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;