我需要一些SQL指导。我有一个表(a(,它看起来像这样(注意的行应该是ID+CommType唯一的(:
成员ID | CommType | 同意可通过电子邮件发送 | |
---|---|---|---|
1 | 新闻 | 是是 | |
2 | 新闻 | 是否 | |
1 | 调查 | 否||
2 | 调查 | 是是 | |
1 | 事件 | 是||
3 | 新闻 | 否 | 否
您可以尝试以下操作-注意,这假设您有合理数量的不同CommTypes,并且您知道所有可能的值:
SELECT member_id
, MIN(CASE WHEN CommType='News' THEN CASE WHEN Consent = 'Yes' AND Emailable='Yes' THEN 'Yes' WHEN Emailable = 'No' THEN 'No' END END) AS news
, MIN(CASE WHEN CommType='Surveys' THEN CASE WHEN Consent = 'Yes' AND Emailable='Yes' THEN 'Yes' WHEN Emailable = 'No' THEN 'No' END END) AS surveys
, MIN(CASE WHEN CommType='Events' THEN CASE WHEN Consent = 'Yes' AND Emailable='Yes' THEN 'Yes' WHEN Emailable = 'No' THEN 'No' END END) AS events
-- , ..., do the same for each possible CommType
FROM your_table
GROUP BY member_id