Solved

Gernate multiple rows from a single row in stored procedure

Posted on 2008-06-16
7
169 Views
Last Modified: 2010-04-21
I have a table which contains details for upto 4 applicant in the same family. I would like to be able to geneate a single row for each applicant so that I can use parmeters to select / deselect them individually.
i.e if I want to extract everyone over 40.
2 applicants might over 40 and the other 2 under 40.
I would have to read 1 records and generate 2 rows from it. (or maybe 3 etc etc)

Any ideas
ps I am using SQL Server 2000 with an Access 2003 adp
0
Comment
Question by:EWHTLC
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 21792163
Use a 'helper' table with with 4 rows in it and put in a Case for each piece of data that can occur more than once. The attached example returns two rows
declare @Nums table(Num Integer)

Insert @Nums Values(1)

Insert @Nums Values(2)

Insert @Nums Values(3)

Insert @Nums Values(4)

Declare @MyTable table (N1 varchar(50), A1 int, N2 Varchar(50), A2 Int, N3 varchar(50), A3 int, N4 Varchar(50), A4 int)

Insert @MyTable Values('Mom', 45, 'Dad', 46, 'Son', 20, 'Daughter', 22)
 

Select TheName, TheAge

From (Select 

		Case Num When 1 then N1 When 2 then N2 When 3 then N3 When 4 then N4 end as TheName,

		Case Num When 1 then A1 When 2 then A2 When 3 then A3 When 4 then A4 end as TheAge

	  From @MyTable, @Nums) as ManyRows

Where TheAge > 40

Open in new window

0
 

Author Comment

by:EWHTLC
ID: 21792182
Thanks Mike, I'll give that a whirl as sson as I can.
0
 

Author Comment

by:EWHTLC
ID: 21793330
I'm having a few problems understanding this. However, this may be because of how i've explained it.
let me explain a better scenario.
I haev a table tbl_Case.
CASE_CASE_NO = 123456
CASE_APP   = "Mr and Mrs Smith"
CASE_APP1 = "Bill Smith"
CASE_AGE1 = 45
CASE_APP2 = "Joan Smith"
CASE_AGE2 = 42
CASE_APP3 = "Jimmy Smith"
CASE_AGE3 = 15
CASE_APP4 = "Molly smith"
CASE_AGE4 = 11

I'd like to generate (as a recordsource to an Access report) any applicant over 40 years old

So the rows generated would be as follows :-

Row 1          123456, Bill Smith, 45
Row 2          123456, Joan Smith, 42

I might be missing something but hopefully this should make it clearer.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 21795021
Remove the lines that create an insert to @MyTable, they were just there to provide some test data. Use tbl_Case in place of @MyTable in the inner select and use your own name and Age column in the Case statements
declare @Nums table(Num Integer)

Insert @Nums Values(1)

Insert @Nums Values(2)

Insert @Nums Values(3)

Insert @Nums Values(4)
 

Select CASE_CASE_NO, TheName, TheAge

From (Select 

         CASE_CASE_NO,

	Case Num When 1 then CASE_APP1 When 2 then CASE_APP2 When 3 then CASE_APP3 When 4 then CASE_APP4 end as TheName,

	Case Num When 1 then CASE_AGE1 When 2 then CASE_AGE2 When 3 then CASE_AGE3 When 4 then CASE_AGE4 end as TheAge

      From tbl_Case , @Nums) as ManyRows

Where TheAge > 40

Open in new window

0
 

Author Comment

by:EWHTLC
ID: 21795525
That is absolutely Amazing.
Spot on, and it works a treat.
Thank you very much you have saved me loads of time (and a possible re design)
0
 

Author Closing Comment

by:EWHTLC
ID: 31467508
An amazing answer.
I didn't think it was possible.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21795542
Thanks, glad to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing value to a stored procedure 8 91
Access Date Query 28 44
Oracle query output question 4 36
Update data using formula 22 21
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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