CODEWITHSUNDEEP
sql-servermergeunique
Phteven
Phteven

Reputation: 139

How can I squash these SQL Server results into one row per customer key

I've managed to gather our customers latest marketing preferences into the following form but can't figure out how to get these results to compress into one row per [CustomerKey]. Here's an example, obviously we have hundreds of thousands of actual results when I run the query further below.

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

This is the rather repetitive query I used to create the above just in case anyone looks at it and thinks I'm coming at this from entirely the wrong angle;

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

Upvotes: 1

Views: 752

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Window function should do it 

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

Upvotes: 0

Eric
Eric

Reputation: 3257

The easiest way will be to wrap your query in another GROUP BY

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

Upvotes: 2

Related Questions