Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

MS SQL 2008 Query or view - multiple rows to single row help

I have a database with several normalized tables.  In my customer table I have customer id, fname, lname.  In my emergency contact table I have customer id, contact phone.  I have anywhere from 1 to 10 emergency contact phone records for each customer ID.  I need to create a view or a query that will return records as one row.

I would like the first 3 distinct contact phone numbers to appear like this:

customer id, fname, lname, econtact#1, econtact#2, econtact#3

Obviously if they have 0, 1, or 2 emergency contact records then #3, #2, or #1 will may be blank since there may not be that many related emergency contact phone records.  But I have seen as many as 10 emergency contact records but I only want 3 - top 3 or first 3 returned will be fine.

THanks for your help.

Adam
0
afeind
Asked:
afeind
  • 3
  • 3
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20923957.html

Use a UDF.  Pass in your customer id, returning your top 3 concatenated values.

then you can call it like:

select customerid, fname, lname, fn_getENumbers(customerid) from customers
0
 
cyberkiwiCommented:
;with x as (
select a.id, a.fname, a.lname, b.phone,
rn=row_number() over (partition by a.id order by a.id)
from customer a left join contact b on a.id=b.customer_id)
select t1.id, t1.fname, t1.lname, t1.phone as phone1, t2.phone as phone2, t3.phone as phone3
from x t1
left join x t2 on t1.id=t2.id and t2.rn=2
left join x t3 on t1.id=t3.id and t3.rn=3
where t1.rn=1
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Create Function fn_GetENumbers(customerid int)
returns varchar(100)
as
begin
DECLARE @Numbers varchar(100)

SELECT top3 @Numbers = COALESCE(@numbers + ', ', '') +
   CAST(contact_phone AS varchar(15))
FROM emergency_contact
WHERE customerid = @customerid

return @Numbers
end
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
afeindAuthor Commented:
cyperkiwi - is there something missing from your example?

I am not sure I understand this solution.

I tried the function solution and could not get it to work.
0
 
cyberkiwiCommented:
that query is as-is, assuming you have supplied the correct table and field names.
It should just be copy and paste
0
 
afeindAuthor Commented:
Cyberkiwi - you are a true Genius - it works great.  One more question is there any way to display only distinct phone numbers?

For instance the emergency contact 1 may have the same number as emergency contact 2 - crazy I know but the customer will have emergency contact 1 as John Doe and emergency contact 2 as Jane Doe - both with the same number so rows may be like this
customer, number
1111, 555-1212
1111, 555-1212
1111, 555-1313
1111, 555-1313
1111, 555-1414
1111, 555-1818

would it be possible to return just first 3 distinct rows of phone numbers as t1, t2, t3?

Thanks!!!
0
 
afeindAuthor Commented:
Solution works flawlessly.  Thank you!
0
 
cyberkiwiCommented:
This should do it.

;with x as (
select a.id, b.phone,
rn=row_number() over (partition by a.id order by a.id)
from customer a left join (select distinct top 3 id, phone from contact) b on a.id=b.id)
select t1.id, t1.phone as phone1, t2.phone as phone2, t3.phone as phone3
from x t1
left join x t2 on t1.id=t2.id and t2.rn=2
left join x t3 on t1.id=t3.id and t3.rn=3
where t1.rn=1

Regards
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now