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

1 Answer 1

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.

4
  • Thanks, is there a way to identify customers who bounce on consecutive months?
    – Peter Ho
    May 5, 2018 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? May 5, 2018 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, 2018 at 13:26
  • Great example ! May 11, 2018 at 7:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .