MS SQL 2008 Query or view - multiple rows to single row help
Posted on 2010-11-22
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.