PUTTING MULTIPLE RECORDS INTO ONE 2

[LeonelSanchezJr]

I AM WRITING A STORED PROCEDURE TO EXTRACT DATA FROM TWO TABLES: BASEINFO AND ADDRESSES.

EXAMPLE: BASEINFO HAS THE PERSONAL INFORMATION. THE ADDRESSES TABLE HAS ALL OF THE PREVIOUS ADDRESSES FOR THE PERSONAL INFORMATION. WHAT HAPPENS IS I GET 4 RECORDS BACK. I WOULD LIKE TO LIMIT IT TO ONE RECORD WITH ONLY THE FIRST FOUR RECORDS FROM THE ADDRESS TABLE AS ADDR1, ADDR2, ADDR3, ADDR4.

PLEASE HELP.

THANKS

 

[Andel]

"I WOULD LIKE TO LIMIT IT TO ONE RECORD WITH ONLY THE FIRST FOUR RECORDS.."

I'm sorry, can you explain this? Better yet, provide us sample data and sample result of your query.
Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[LeonelSanchezJr]

BASEINFO TABLE

ID NAME TELEPHONE
123 LEO 713-555-555



HOMEADDRESS TABLE

ID ADDRESS
123 1234 SOMEWHERE
123 4002 FLAMINGO
123 316 NORTH 16TH
123 2007 SILVER LANE


DESIRED OUTPUT WOULD BE:

ID NAME ADDR1 ADDR2 ADDR3 ADDR4
123 LEO 1234 SOMEWHERE 4002 FLAMINGO 316 NORTH 16TH 2007 L


BASICALLY IN ONE RECORD WITH FOUR ADDRESS FIELDS.

 

[foxdev]

I think I get it - he wants to pivot the address data.

Off the top of my head, the only way I can think of is to select the TOP 4 records into a cursor, then loop through them while updating a #temp table's columns 1 through 4.

Leonelsanchezjr, all caps is difficult to read.

Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[LeonelSanchezJr]

Correct. That is what I would like to do, but I don't know the logic to accomplish that. Can anyone help? I've tried reading through the SQL 2000 Help files, but they weren't any help. Thank you for your time and cooperation.

 

[Andel]

Hi, try this...

select
ID,
Address1 = (select top 1 address + ' ' + addresscsz from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id),
Address2 = (select top 1 address + ' ' + addresscsz from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id and
address not in (select top 1 address from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id)),
Address3 = (select top 1 address + ' ' + addresscsz from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id and
address not in (select top 2 address from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id)),
Address4 = (select top 1 address + ' ' + addresscsz from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id and
address not in (select top 3 address from homeaddress a, baseinfo n where n.id = a.id and n.id = x.id))
from
baseinfo x Andel
andelbarroga@hotmail.com

http://andyb.8m.com