MS SQL Multi Selects


Not sure if this is possible but here is the question.

We have MS SQL Table with a Answer , ItemID , ReportRef and ReportType Fields.

SQL Sample TableThis 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

Open in new window

Hope that makes sense.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

QuinnDexConnect With a Mentor Commented:
try this

SELECT ReportRef,
				Min(Case WHEN ItemID = 1 THEN Answer End)[ref] ,
             Min(Case WHEN ItemID = 2 THEN Answer End)  [date],
            Min(Case  WHEN ItemID = 3 THEN Answer End) [person],
             Min(Case WHEN ItemID = 4 THEN Answer  End) [undifined],
             Min(Case WHEN ItemID = 5 THEN Answer  End) [address],
             Min(Case WHEN ItemID = 6 THEN Answer  End) [name],
             Min(Case WHEN ItemID = 7 THEN Answer  End) [text],
             Min(Case WHEN ItemID = 8 THEN Answer End) [time]            
          into [NewTAble]
          Where ReportType = 1 
          Group By ReportRef

Open in new window

Not quite sure what it is you are asking for, this will pull the data into NewTable accordding to reporttype and reportreff, if this isnt what you want give me some more details

select into [NewTable] Answer , ItemID , ReportRef and ReportType from MainTable Where ReportType = 1 and ReportRef =22222

select * from NewTable

Open in new window

BoltonWandererAuthor Commented:

Thanks for the quick response. Sorry for the bad explanation I gave.

What I would want is how can I get that table from the screenshot into a table which looks like this? The colours show where the information got it from.

End Result
Currently what I am doing is running a query which gets the information in ItemID 1, save as variable, then get the information in ItemID5, save as variable. This loops round until I have the 5 pieces of information I need. As the ITEMID is dynamic I do a lookup to another table which says where the ItemID will be based on the reportType.
So it goes like:
Get ITEMID location for ReportType X and the Report ID.> Get Information based on variable before > Save information in variable Then move onto the next ItemID I need and repeat.
Then I do an INSERT INTO NEWTable(ReportRef,ReportType,Ref,Address,Date,Name,Person)  VALUES ('22222','1','this is a test ref','test address','23/11/2013','name','person').

So in the longer term I want it to be able to look like this with a very small amount of select/insert queries to archieve this.
Let me know if that makes more sense please?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

I see, so you have a table with all the answers in the same column and you want to seperat them out into a single row per person,

is that correct
BoltonWandererAuthor Commented:
Yes it is. Is it possible?
BoltonWandererAuthor Commented:
Excellent just what I needed thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.