Hello! I'm not very good with SQL really, just getting started. My background is with traditional dBase style database programming. I've been somewhat successful via pounding my head on the wall and researching and researching, but I've pounded my head on this one enough and need some guidance.

The source data is medical diagnosis information, however I have to product a report in specific format for "registry" for accreditation, and they need me to "flatten" out the data as you'll see.

The source data looks like this. There can be multiple rows of data for a single patient visit date. Each row for a single patient visit has a diagnosis code and the position of that diagnosis code, as there are typically multiple diagnosis identified for a single patient visit. Hope this makes sense:

Pat # Pat SSN Pat DOB Pat Sex ….. Service Date Service Diagnosis Position Diagnosis Code
12345 987654321 1/1/1980 M 8/1/2011 1 427.31
12345 987654321 1/1/1980 M 8/1/2011 2 V04.81
12345 987654321 1/1/1980 M 8/1/2011 3 202.8

Of course there is much more data. Ok, so see the 'Service Diagnosis Position', a numeric field? It's the second to last field (1,2,3). In my source data, each diagnosis for a visit record is stored in a separate record. I need to order the data by Pat #, Service Date, and then break out the Diagnosis codes like this:

Pat # Pat SSN Pat Birthdate Pat Sex Pat Age Pat Addr1 Pat Addr2 Pat City Pat State Pat Zip5 Home Phone From DOS Diag1 Diag Name Last Name First Name Middle Initial Location Diag2 Diag3 Diag4 Diag5 Diag6 Diag7 Diag8 Diag9 Diag10 RACE

So it doesn't look very good all wrapping like this, but note that if the 'Service Diagnosis Position' is a 1, then the Diag1 field would get the 'Diagnosis Code' from the source data. If the 'Service Diagnosis Position' is a 2, then Diag2 would get the 'Diagnosis Code' from the source, and so on with Diag3, Diag4, Diag5.....

So essentially I need to group by the patient and date of service into one row, with the diagnosis information broken out on that row. I just don't know how to do this. I assume it will be with a subquery, but I am struggling with those, having only done one successfully so far.

I have created all the Diag2, Diag3, etc expression conditional fields, but how would I have them 'summed' or all brought down into the one patient/visit row? Obviously First and Last won't work.

Any ideas and help is greatly appreciated. Thank you!!!