Gernate multiple rows from a single row in stored procedure

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
Eric HarrisDeveloperAsked:
Who is Participating?
 
MikeTooleConnect With a Mentor Commented:
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
 
MikeTooleCommented:
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
 
Eric HarrisDeveloperAuthor Commented:
Thanks Mike, I'll give that a whirl as sson as I can.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Eric HarrisDeveloperAuthor Commented:
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
 
Eric HarrisDeveloperAuthor Commented:
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
 
Eric HarrisDeveloperAuthor Commented:
An amazing answer.
I didn't think it was possible.
0
 
MikeTooleCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.