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.