0

I have two tables:

  1. STUDENTS
  2. APPLICATION

A student can apply to 1-3 programs. Student_ID is a column on Application.

Table structure

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

Expected results

First     | Last     | Program1        |  Program2 | Program3
Robert    | De Niro  | ComputerScience |  Math     | Cinema
Sponge    | Bob      | Theatre         |  Cinema   | 

Questions

  1. 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?

  2. 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.

Looked into PIVOT. I found that it is used with aggregation operators (AVG, COUNT, MIN, MAX, SUM, etc.) - which is not the case here.

Browse other questions tagged or ask your own question.