Picking rows with partitions
I’m constantly faced with situations with data in Salesforce Marketing Cloud where I need to select or identify rows based on some ordered criteria. The row_number() over (partition by x order by y) as ranking is my go-to method.
This particular query finds a random row for each bounce category in the _bounce data view:
Recently, found this shorter method:
Credit @TheGameiswar
Package Thief vs Glitter Bomb
Pontiac GTO Tri-Power 389 V-8 Engine Rebuild Time-lapse
Another very well done engine rebuild time-lapse. This time it’s a Pontiac GTO Tri-Power 389 V-8.
Parsing Delimited Fields Values with SQL Cross Apply
Say you wanted parse some field values:
SubscriberListMembership
| UUID | Lists |
|---|---|
| AAAA | 1|2|3|4|5|6|7 |
| BBBB | 2|4|6|8|10|12|14 |
| CCCC | 1|2|3 |
…and output something like this in Marketing Cloud:
SubscriberListMembership_Parsed
| UUID | Lists | List1 | List2 | List3 | List4 | List5 | List6 | List7 |
|---|---|---|---|---|---|---|---|---|
| AAAA | 1|2|3|4|5|6|7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| BBBB | 2|4|6|8|10|12|14 | 2 | 4 | 6 | 8 | 10 | 12 | 14 |
| CCC | 1|2|3 | 1 | 2 | 3 |
Since you can’t use any of the newer T-SQL functions for parsing in Marketing Cloud, you can use Cross Apply to determine the positions of the delimiters and then simple sub-strings to extract each one.