你可以先将三个字段“Consumer"、“Producer”和“Outsider”展开为一个单独的字段，每个字段都有区别，然后使用STRING_AGG对每个日期和人物应用字符串聚合。

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

查看here演示。