Solved

MS SQL Multi Selects

Posted on 2013-11-23
6
241 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now