Bind rows with same index

[maaaaa]

I have a table as shown:

movie_id	title	Job	Name
285	Pirates if the Caribbean	Director	Gore Verbinski
285	Pirates if the Caribbean	Director	Chad Oman
285	Pirates if the Caribbean	Producer	Eric McLeod
285	Pirates if the Caribbean	Producer	Jerry Bruckheimer
285	Pirates if the Caribbean	Producer	Pat Sandstron
559	Spider-Man 3	Director	Sam Raimi
559	Spider-Man 3	Producer	Avi Arad
559	Spider-Man 3	Producer	Grant Curtis

I have Like to Have an Output like this:

movie_id	title	Director 1	Director 2	Producer 1	Producer 2	Producer 3
285	Pirates of the Carribean	Gore Verbinski	Chad Oman	Eric McLeod	Jerry BruckHeimer	Pat Sandstron
559	Spider-Man 3	Sam Raimi	Avi Arad	Grant Curtis

But I don't know how many Directors and Producers there are per Movie

Is there any way to match the movies by id and bind them with their Director and Producer?

[Dalton_Ruer · Accepted solution]

 Step 1: Concatenate your Directors and Producer names into a single field value separated by commas. Notice that there is a preceding load for the Directors and Producers table that gets the count of directors/producers for each movie row.

Data:
Load * Inline [
movie_id, title, "job name"
285 Pirates if the Caribbean, Director, Gore Verbinski
285 Pirates if the Caribbean, Director, Chad Oman
285 Pirates if the Caribbean, Producer, Eric McLeod
285 Pirates if the Caribbean, Producer, Jerry Bruckheimer
285 Pirates if the Caribbean, Producer, Pat Sandstron
559 Spider-Man 3, Director, Sam Raimi
559 Spider-Man 3, Producer, Avi Arad
559 Spider-Man 3, Producer, Grant Curtis
];

Directors:
Load *, SubStringCount(Directors,',')+1 as DirectorCount;
Load movie_id, Concat("job name", ',') as Directors
resident Data
where title = 'Director'
group by movie_id;

Producers:
Load *, SubStringCount(Producers,',')+1 as ProducerCount;
Load movie_id, Concat("job name", ',') as Producers
resident Data
where title = 'Producer'
group by movie_id;

Drop Table Data;

Step 2. You can display Director/Producer 1, 2, 3 etc by using the SubField function: SubField(Producers, ',', 4)  Then label each column as Director 1, Director 2 etc. Go a few above whatever you think your maximum might be. 

Step 3. You can use the Show Column If condition to determine whether to show the column or not with an expression like the following where you would change the number for each column. It means "Show Producer 4 if the Maximum ProducerCount for any movie is greater than or equal to 4." Notice in the image based on your data it says the Calculation Condition is not met so Producer 4 does not show. 

Max(ProducerCount)>=4

[maaaaa]

Got it working there thanks so much