rows to columns

I really didn’t think i could do this, so i am going to write this up. I zm using a while loop, and not the more preferred set operation. Maybe some day, i’ll come back and fix it.

We have a database on patients. We have a second database that has ‘extra data’ which is another screen but which is row, not column, oriented. it is easy to add another field (which shows up on the input screen, but which is no always used.” So the table does not, as with the patients, have nulls for each field when there is no entry: the table for extra data might not have anything, or just one entry. Or they can all be filled in. The key is that the table for patients is wide with one row per patient, and the table for extra data is tall – zero to many rows for a patient.

Of course, we want it all so i need to denormalize the extra-data and put it into a query table so all the patient data is in a row (as well as other data from other tables). One, or as few as possible, stop for each patient. Depending on the GUI tool.

Step one was get a list of patients into a table.

select distinct patient_id into #myfields
from [database].[dbo].[Patient_Info] pi

Step two is get a list of all extra-data names and add a column for each to the existing patient name table from step one. This alter table causes no issues. for this, i did a loop over a temp table with an identity column, a pointer local variable, and incremented it each loop, using column name and null. I identified each column as varchar, although I did have enough information to be able to create them as int or date, or whatever. Later, gator.

set @SQL = 'alter table #myfields 
add [' + @column_name + '] varchar(50) null'

At this point, i have a temp table with a column of patients and all the other columns blank/null, but named. And a table with each extra-data entry by entry-type name (like Mother’s Maiden Name’ – and yes, the developer’s put that apostrophe in the column name).

From the initial extra data table, i can get each patient and the field name and the field value. Sounds like a cursor. Or i could try to get all the data for each patient into a single update statement, with suitable nulls for no data? Or i could try to join the patients and a column, one at a time and fill in values and then update the table with the output, one column name at a time.

What i did was to loop over the names in the name table, stored each loop in @fieldName. Select the patient id, the value and the field name where the field name is that of the loop into a new temp table.

select pi.patient_id, pi.field_value, pif.field_name 
into #mytemp2
 from [database].[dbo].[Info] pi
 join [database].[dbo].[Info_fields] pif 
on pi.Info_Field_ID = pif.Info_Field_ID
 where pif.field_name = @fieldName

It will have as many rows as there are entries for that field name in the extra info table. Use dynamic SQL to update the first temp table where the column name is the field name and the patient id the the row and the value is the value.

set @SQL = 'update #myfields set 
#myfields.[' + @fieldName + '] = #mytemp2.field_value 
from #myfields inner join #mytemp2 
on #mytemp2.patient_id = #myfields.patient_id'

Do this for each of the extra fields names. Write the updated temp table to the desired output table, and go home.

Advertisements

time after time

one table has a datetime in it.  The other has a dateID that is an … integer.  Sheesh.

I need to include the date into the join.  That is – i have a userid in the first table and want to join it to the second table, but i have LOTS of that userid in table two.  So i need also to restrict the join to the same date.  Turns out THAT isn’t even good enough, but don’t go there.

We need to coerse the integer into a date.  That we can accomplish with a cast as in

cast(dp.OfficeVisitDateID as datetime).  

Doesn’t work.  Can’t cast an integer this way.  First need to cast the integer into a varchar, then cast that into a datetime.  This works

(cast dp.OfficeVisitDateID as varchar(12)) as date)

oh,  you noticed?  the format of the the integer is 20130823, which means that we are ‘insecure’ about compairing that to something like ”2013-08-23 09:40:00.000”, so i am going to cast the datetime to a date so that i am more comfortable with comparing the two.

cast (@pm.appointment_datetime as date)

I tried to substract the two dates, but the result, when i expected them to be 0 was 1900-01-01 00:00:000, which i guess makes perfect sense, but harder to program.  The importance is that a join using the two dates works, so all is well in T-SQL Land.