How to Separate fields with same ID into two columns

[Anonymous]

I have the following data in my QV table

ID	Subject
1234	English
1234	Maths
2345	French
2334	English

I would my table to show distinct ID and associated subjects - so this is want I'd like to output

ID	Subject1	subject 2
1234	English	Maths
2345	French
2334	English

Any suggestions please

Thanks

JazzyK

[andydietler · Accepted solution]

Are there only a maximum of 2? You could create a more dynamic loop if there are more, but this should work for two:

NewTable:

LOAD

     ID,

     Subject AS Subject1

Resident CurrentTable

WHERE Not ID = Previous(ID)

ORDER BY ID, Subject;

Left Join (NewTable)

LOAD

     ID,

     Subject AS Subject2

Resident CurrentTable

WHERE ID = Previous(ID)

ORDER BY ID, Subject;

[Anonymous]

No I have 4870 IDs and 4999 subjects. I just provided a sample. I really wanted do this as an expression in a table.

Thanks

Jasmit

[Anonymous]

Thank you. That worked perfectly. I managed to put it into the script.