Solved

MS SQL Multi Selects

Posted on 2013-11-23
6
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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