Solved

MS SQL Multi Selects

Posted on 2013-11-23
6
235 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

13 Experts available now in Live!

Get 1:1 Help Now