Solved

Gernate multiple rows from a single row in stored procedure

Posted on 2008-06-16
7
181 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
Industry Leaders: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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