I would look like to build a SQL query that follows the below: I have a table such as:

Based on the role type, I want to check if that person has any other role in another column and output a unique role name. A person can have a different role on a different transaction date. The persons role should not be repeated for the same TransactionID/Date. Example Output:

CodePudding user response：

You can do it by first unrolling your three fields "Consumer", "Producer" and "Outsider" into one single field, distinctively per field. Then apply string aggregation with STRING_AGG for each date and person.

WITH cte AS (
    SELECT DISTINCT consumer AS PersonName, 
                    'Consumer' AS UniqueRole, 
                    TransactionDate
    FROM tab
    UNION ALL
    SELECT DISTINCT producer AS PersonName, 
                    'Producer' AS UniqueRole, 
                    TransactionDate
    FROM tab
    UNION ALL
    SELECT DISTINCT outsider AS PersonName, 
                    'Outsider' AS UniqueRole,
                    TransactionDate
    FROM tab
)
SELECT PersonName, 
       STRING_AGG(UniqueRole, '-') AS UniqueRole,
       TransactionDate 
FROM cte
GROUP BY PersonName, TransactionDate
ORDER BY UniqueRole

Check the demo here.