creating one row out of several rows

[nuct]

I want to change this:

STUDENT COURSE
kevin fine art
kevin 3d design
kevin pottery

to this:

STUDENT COURSE(s)
kevin fine art, 3d design, pottery

Is this possible?

Thanks,

Simon.

 

[PruSQLer]

The only way I know of to do this is:

Select student,
min(case when course = 'fine art' then 'fine art'
else ' ' end),
min(case when course = 3d design' then '3d design'
else ' ' end),
min(case when course = 'pottery' then 'pottery'
else ' ' end),
min(case when course = 'sql' then 'sql'
else ' ' end)
From Table
Group by student

Of course, if you have 500 values for 'course', this isn't so desirable but if you only have 30 or so, it's not that hard to implement.

 

[baltman]

I can describe how I would programatically do this, but it wouldn't be using SQL.

I'd sort the table by student and then scan through the file and save the courses into a delimited string.

Each time the student changes, save the string as a line in an output file.

Brian

 

[nuct]

Thanks I'll give these a go.

Simon