Solved

Gernate multiple rows from a single row in stored procedure

Posted on 2008-06-16
7
182 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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