I have two tables:
A student can apply to 1-3 programs.
Student_ID is a column on Application.
APPLICATION ID | Student_ID | ProgramName 1 | 65432 | ComputerScience 2 | 65432 | Math 3 | 65432 | Cinema 4 | 65431 | Theatre 5 | 65431 | Cinema etc...
The end goal is to get to this:
1 student per row, with all their application programs
First | Last | Program1 | Program2 | Program3 Robert | De Niro | ComputerScience | Math | Cinema Sponge | Bob | Theatre | Cinema |
Can this be achieved with one SQL statement?
What would it be?
If not, what would be the approach (and queries) to use an intermediate table?
If we're not sure each student has exactly 3 applications, (can have one or two) - does this change anything?
I am using SQL Query Activity on Salesforce Marketing Cloud. The documentation says:
SQL support for the SQL Query Activity is based on, but does not precisely correspond to, SQL Server 2005 capabilities.
PIVOT. I found that it is used with aggregation operators (
SUM, etc.) - which is not the case here.