Solved

Gernate multiple rows from a single row in stored procedure

Posted on 2008-06-16
7
166 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

16 Experts available now in Live!

Get 1:1 Help Now