如何将这些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