Not sure if this is possible but here is the question.
We have MS SQL Table with a Answer , ItemID , ReportRef and ReportType Fields.
This Table holds all the answers to a form which is filled in, based on the Report Type, the ItemID can be different e.g. Report Type 2 would have the address in number 4 instead of 5 etc. So I understand if I have to do a new SELECT query / loop based on the number of reports.
What I am currently doing is looping through a master table with all the report references (Being unique) then for each item I require I find the correct information save it to a variable and then collect the next item I need etc. Once I have the 3 references I then do an INSERT into a new table which has the 5 columns in which I need.
When testing this, it takes about 2 seconds per record and there is about 50,000 records for the first import, then after that maybe around 100 a day (so the 2 seconds shouldn't matter) however I am wanting to know if I am doing this correctly, is this the best option?
Is there a way I can do a select query which gets the data I need based on a reporttype where clause. e.g.
SELECT ReportRef, (SELECT Answer from TableA where ItemID =1), (SELECT Answer from TableA where ItemID =2), (SELECT Answer from TableA where ItemID =3) FROM TABLEB
Then join the ReportRef from TABLEB to TABLEA so that it doesn't return incorrect data based on each report.
Where ReportType = 1
Hope that makes sense.