如何将这些 SQL Server 结果压缩为每个客户密钥的一行

Question

我已经设法将客户最新的营销偏好收集到以下表格中，但无法弄清楚如何将这些结果压缩到每[CustomerKey]一行中。下面是一个示例，显然，当我在下面进一步运行查询时，我们有数十万个实际结果。

CustomerKey                 SMS   Phone   Email   Post   Group
--------------------------------------------------------------
ClarkeM21-9EF1976-08-03                                  No
ClarkeM21-9EF1976-08-03                           No
ClarkeM21-9EF1976-08-03                   No
ClarkeM21-9EF1976-08-03           No
ClarkeM21-9EF1976-08-03     No
SmithNG12-8AS1980-02-03                                  No
SmithNG12-8AS1980-02-03                           No
SmithNG12-8AS1980-02-03                   Yes
SmithNG12-8AS1980-02-03           No
SmithNG12-8AS1980-02-03     Yes

这是我用来创建上述内容的相当重复的查询，以防万一有人看到它并认为我从完全错误的角度来解决这个问题;

IF OBJECT_ID('tempdb..#ClientRefPreferenceDates') IS NOT NULL 
DROP TABLE #ClientRefPreferenceDates;
IF OBJECT_ID('tempdb..#MaxDatePerClientRef') IS NOT NULL 
DROP TABLE #MaxDatePerClientRef;
IF OBJECT_ID('tempdb..#LatestPrefsbyCustKey') IS NOT NULL 
DROP TABLE #LatestPrefsbyCustKey;
SELECT  
mcp.[CustomerKey],
mcp.[SMS],
MAX(mcp.[SMS_DateTime]) AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
INTO 
#ClientRefPreferenceDates
FROM 
[audit].[Marketing_Consent_Prefs] mcp
WHERE 
mcp.[EndDate] IS NULL
GROUP BY 
CustomerKey, SMS
UNION ALL
SELECT  
mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
mcp.[Phone],
Max(mcp.[Phone_DateTime]) AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM
[audit].[Marketing_Consent_Prefs] mcp
WHERE 
mcp.[EndDate] IS NULL
GROUP BY  
CustomerKey, Phone
UNION ALL
SELECT  
mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
mcp.Email,
MAX(mcp.[Email_DateTime]) AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM 
[audit].[Marketing_Consent_Prefs] mcp
WHERE 
mcp.[EndDate] IS NULL
GROUP BY CustomerKey, Email
UNION ALL
SELECT  mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
mcp.Post,
Max(mcp.[Post_DateTime]) AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY CustomerKey, Post
UNION ALL
SELECT  mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
mcp.[Group],
Max(mcp.[Group_DateTime]) AS GroupDate
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY CustomerKey, [Group]
SELECT  CustomerKey,
SMS,
Phone,
Email,
Post,
[Group]
FROM #ClientRefPreferenceDates 
GROUP BY CustomerKey, SMS, Phone, Email, Post, [Group]
ORDER BY CustomerKey

Answer 1

最简单的方法是将查询包装在另一个GROUP BY

SELECT CustomerKey, MAX(SMS), MAX(Phone), MAX(Email), MAX(Post), MAX(Group)
FROM (
your query
) a
GROUP BY CustomerKey

Answer 2

窗口函数应该这样做

SELECT  distinct 
mcp.[CustomerKey],
mcp.[SMS],
Max(mcp.[SMS_DateTime]) over   (partition by CustomerKey, SMS)  AS SMSDate,
Max(mcp.[Phone_DateTime]) over (partition by CustomerKey, Email) AS PhoneDate
INTO #ClientRefPreferenceDates
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY