Merge rows in outer cell

[graabein]

Hi, kind of simple question I need help with... I'm a bit inexperienced with some parts of SQL so...

I have a query that looks like this (simplified):

select a.Name, b.Date, c.Operations
from a inner join b on a.PatientID=b.PatientID 
left outer join c on b.ID=c.ID
where year(b.Date) > 2003 order by a.PatientID

This returns patients with their check-up dates and what procedures was used on the check-up. But I want to collect each patient in one row, not for example three rows for some patients and one for others. I want to cramp the operations in one large string cell besides the patient name and the check-up date.

Example:

Name             | Date                | Operations
Jimi Hendrix     | 2004-01-12 00:00:00 | NULL
Townes Van Zandt | 2004-02-26 00:00:00 | 6
Neil Young       | 2004-01-20 00:00:00 | 1
Neil Young       | 2004-01-20 00:00:00 | 2

Pretty straight forward task I'm sure, but I don't know how and the online tutorials I've seen are to no help. My employer doesn't have SQL books either, so any help would be much welcome!


graabein

 

[PruSQLer]

The most common way to do this is as such

select a.Name, b.Date
, max(case when c.Operations = 1 then 1 else null end)
, max(case when c.Operations = 2 then 1 else null end)
, etc etc
, max(case when c.Operations = "MAX NUM of Operations" then MAX NUM else null end)
from a inner join b on a.PatientID=b.PatientID
left outer join c on b.ID=c.ID
where year(b.Date) > 2003
Group by a.Name, b.Date
order by a.PatientID

This of course will give you fixed fields according to the number of operations possible. I don't know how to make it variable, e.g. Name Date 2 5 6 without some kind of approach using stages.

I guess there was no point operating on Jimi, eh?

 

[rac2]

So often folks want to move rows into columns or multiple rows into single rows. This is not really a SQL operation, although it is possible sometimes as PruSQLer shows. This kind of thing is appropriate to the application where data is formatted for display.

 

[graabein]

Hmmm, this was not what I wanted I think...

I wanted the output to be like this:

Name             | Date | Operations
Jimi Hendrix     | x    | ""
Townes Van Zandt | x    | "6"
Neil Young       | x    | "1, 2"

 

[PruSQLer]

That's why rac2 said it's not really a SQL operation and I said I don't know how to do a variable-type record result without using a multi staged approach. SQL (obviously) is a database language designed to work with rows and columns. A programming language such as VB, working with a text file from a RDB, could easily do what you need.

I've actually done things like you describe above using SQL but I use DB2 which permits the use of Table Expressions that are really just temporary tables you create throughout the execution of a SQL query. I don't know if the RDB you're using allows something like that.