1

I'm trying to create a pivot in MC but keep getting an error when saving SQL can you confirm if MC support pivots? the error i'm getting is "An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax"

SELECT 
SubscriberKey,Month
FROM PH_TOTAL_BOUNCES
PIVOT
(
SubscriberKey
FOR [month] IN (December,January,February, March,April,May)
) AS pvt
3

SFMC does not support pivots, but you can do something like this:

select
subscriberkey
, sum(case when month = 'December' then 1 else 0 end) as December
, sum(case when month = 'January'  then 1 else 0 end) as January
, sum(case when month = 'February' then 1 else 0 end) as February
, sum(case when month = 'March'    then 1 else 0 end) as March
, sum(case when month = 'April'    then 1 else 0 end) as April
, sum(case when month = 'May'      then 1 else 0 end) as May
FROM PH_TOTAL_BOUNCES
group by 
subscriberkey

Here's another example.

  • Thanks, is there a way to identify customers who bounce on consecutive months? – Peter Ho May 5 '18 at 11:58
  • Probably, but it'd be a more complicated query. What's the business case for tracking bounces by month? Some sort of re-engagement campaign? – Adam Spriggs May 5 '18 at 13:11
  • We have a case where if an email bounces a job is triggered to send the customers a letter. I've been asked to identify customers who have had their emails bouncing month on month. so i need to find a way to identify customers who have bounced consecutively each month. – Peter Ho May 5 '18 at 13:26
  • Great example ! – Jeremy Garcia May 11 '18 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.