Solved

MS SQL Multi Selects

Posted on 2013-11-23
6
243 Views
Last Modified: 2013-11-23
Hello,

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

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

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.

Thanks,
0
Comment
Question by:BoltonWanderer
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39671361
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

0
 

Author Comment

by:BoltonWanderer
ID: 39671376
Hi,

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.
Longer
Let me know if that makes more sense please?
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39671380
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
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:BoltonWanderer
ID: 39671386
Yes it is. Is it possible?
0
 
LVL 9

Accepted Solution

by:
QuinnDex earned 500 total points
ID: 39671428
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

0
 

Author Comment

by:BoltonWanderer
ID: 39671477
Excellent just what I needed thank you.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Easy filter aspnet 2 24
MS SQL Server select from Sub Table 14 23
SQLCMD Syntax 2 13
SQL 2012 clustering 9 11
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question