0

I want to move data from rows to columns in a SFMC SQL Automation so that we can easily see all the bookings one customer has made, all in one row.

We collect bookings on one unique reservation number (column 1 in example below) but where a customer has booked in for multiple programs (column 3 and 4), they are currently split out over multiple rows, eg: our DE currently looks like this:

Master DE name: Programs
Reservation |  Name  |   Program      |  Time     |
12345       |  Anna  |   Workshop A   |  9am      |
12345       |  Anna  |   Workshop B   |  9.30am   |
12345       |  Anna  |   Workshop C   |  10am     |
56789       |  Brian |   Workshop D   |  10.30am  |
11223       |  Colin |   Workshop C   |  10am     |
11223       |  Colin |   Workshop A   |  9am      |

And I'd like it to look like where we can see all the programs one customer has booked in one row with the time of the workshop in the column next to the program:

New DE:
Reservation |  Name   | Program1    |  Time1   |  Program2    |  Time2   |  Program3     |  Time3   |
12345       |  Anna   | Workshop A  |  9am     |  Workshop B  |  9.30am  |  Workshop C   |  10am    |
56789       |  Brian  | Workshop D  |  10.30am |  NULL        |  NULL    |  NULL         |  NULL    |
11223       |  Colin  | Workshop A  |  9am     |  Workshop C  |  10am    |  NULL         |  NULL    |

Edit: there are lots and lots of program names that could go in column 3, so it would be great if I didn't have to define each program name.

3
  • 2
    Why do you need them all in a single row? If it's for showing them in a single email, you can use Ampscript's lookuprows function to fetch every occurence of a reservation in a Data Extension, and output it in the email – Lukas Lunow Oct 21 '20 at 11:50
  • Probably try to update those columns with a comma separated value including all the values possible. – mario ruiz Oct 21 '20 at 12:08
  • As a note on this being closed as off-topic. SFMC SQL is limited to specific capabilities only, which makes this ask pertinent to being on SFSE compared to just general SQL, so although this is a SQL based question, it requires the context of SFMC - which is on-topic for this forum. – Gortonington Oct 22 '20 at 12:54
3

As Lukas mentioned, if you are doing this to be able to display information in the email, then DON'T - no need to transpose any data using SQL, as this is exactly what AMPscript is for.

I am assuming you want to display data for each person in an email and that you will have a reservation number in your sendable DE. Then, this should do it:

%%[ 
set @reservation = AttributeValue("Reservation") 
set @rows = LookupRows("Programs","Reservation", @reservation) 
set @rowCount = rowcount(@rows) 

for @counter = 1 to @rowCount do 
set @row = row(@rows, @counter) 
set @program = field(@row,"Program") 
set @time = field(@row,"Time") 
]%%

<br>
Program %%=v(@counter)=%%: %%=v(@program)=%%<br>
Time: %%=v(@time)=%%

%%[ next ]%% 

Using above, you don't need to worry about how many Programs/Reservations each person has, it will display all of them.

Links

LookupRows: https://ampscript.guide/lookuprows/

Loops: https://sfmarketing.cloud/2019/11/29/loops-in-ampscript-and-server-side-javascript/

1
  • Thank you so much - this worked perfectly! Had no idea it could be done with AMPscript. – Gigi Oct 22 '20 at 4:23
4

There are a couple ways that this can work. I personally would use the STUFF method to create a delimited string in a single column instead of trying to do multiple rows.

Here is a previous SFSE answer discussing this.

And here is an article I wrote about this.

This is a sample query based on your above:

SELECT    a.Reservation
        , a.Name 
        , Programs =
        STUFF((SELECT ', ' + b.Program
               FROM (
                        SELECT   sub.Reservation
                               , sub.Name
                               , sub.Program
                        FROM [MasterDEName] sub
                    ) b
               WHERE b.Reservation = a.Reservation
         FOR XML PATH('')), 1, 2, '')
        , Times =
        STUFF((SELECT ', ' + b.Time
               FROM (
                        SELECT   sub.Reservation
                               , sub.Name
                               , sub.Time
                        FROM [MasterDEName] sub
                    ) b
               WHERE b.Reservation = a.Reservation
         FOR XML PATH('')), 1, 2, '')
FROM [MasterDEName] a

Which would output something like:

Reservation |  Name  |   Programs                           |  Times                |
12345       |  Anna  |   Workshop A, Workshop B, Workshop C |  9am, 9.30am, 10am    |
56789       |  Brian |   Workshop D                         |  10.30am              |
11223       |  Colin |   Workshop C, Workshop A             |  10am, 9am            |

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.