Solved

Gernate multiple rows from a single row in stored procedure

Posted on 2008-06-16
7
179 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge two rows in SQL 4 33
table joins in qry 17 82
Formating field in mysql Advance formatting 1 39
MS SQL GROUP BY 6 74
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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