dynamic crosstab based on record count

Question

I have a table "contacts" with

EMPid  ContactID  EmergencyYN  Priority
---------------------------------------
10     20         Y            1
10     39         N            1
10     45         Y            2
11     21         N            2
12     20         Y            2
12     25         Y            1
12     33         Y            3

What I would like is as a result set is

EmpID    EmergencyContact1   EmergencyContact2   EmergencyContact3
------------------------------------------------------------------
10       20                  45
11        
12       25                  20                   33

psuedo sql is as close as I can get:

So n = max number of contacts where EmergencyYN =Y per empID
Then build a string for x = 1 to N
  @sql = @sql + 'EmergencyContact'+x
next

Can some guru help me to do this?

Answer 1

I used a derived query to create the list of distinct EmpID's. Then for each ID returned, I sub-queried to see if there is a matching Emergency & Priority.

SELECT
    A.[EmpID]
    ,(
        SELECT TOP 1 ContactID
        FROM contacts B
        WHERE B.EmpID=A.EmpID AND B.EmergencyYN='Y' AND B.[Priority]=1
    ) AS [EmergencyContact1]
    ,(
        SELECT TOP 1 ContactID
        FROM contacts B
        WHERE B.EmpID=A.EmpID AND B.EmergencyYN='Y' AND B.[Priority]=2
    ) AS [EmergencyContact2]
    ,(
        SELECT TOP 1 ContactID
        FROM contacts B
        WHERE B.EmpID=A.EmpID AND B.EmergencyYN='Y' AND B.[Priority]=3
    ) AS [EmergencyContact3]
FROM (
        SELECT DISTINCT EMPID
        FROM contacts
    ) AS A;