Combine fields?

[mrman2289]

Hi there, not sure exactly how to explain this but here goes.

I have a table containing a student ID and multiple tasks for each student as task1subject, task1, task2subject, task2, task3subject, task3.

I want to know if its possible to build a query which will group the tasks under one name on multiple rows as it were. Giving me an ouput where I can sort by the subject, or if there are any other easy ways of getting the same result, many thanks =)

 

[Golom]

Assuming that task1subject, task1, task2subject, task2, task3subject, task3, etc. are separate fields, you usually do this with a union query

Select StudentID, Task1Subject As TaskSubject, Task1 As Task
From TheTable

UNION

Select StudentID, Task2Subject, Task2
From TheTable

UNION

Select StudentID, Task3Subject, Task3
From TheTable

[blue]etc.[/blue]

Yiu can include IS NOT NULL conditions to eliminate records where there is no data for a Task or TaskSubject.

 

[JarlH]

I'd replace UNION with UNION ALL, just to make sure no rows "disappeares".

(Will probably not happen in this specific case, but may in similar cases.)