Create a new column for each joined record

Question

Agencies may have many contacts.

Agency

id int
name nvarchar(100)

Contact

id int
email nvarchar(100) 
agency_id int

How would you make a Stored Procedure so it returns a resultset that contains each Agency and its Contacts in one row?, so let's say you have one agency with three contacts, you'll end up with.

-----------------------------------------------------------------
| agency_name   | contact_1     | contact_2     | contact_3     |
|---------------------------------------------------------------|
| Foo           | Foo1@Foo1.com | Foo2@Foo2.com | Foo3@Foo3.com |
-----------------------------------------------------------------

It's apparent that one would need to count the MAX amount of joined contacts an agency may have.

score 0 · Accepted Answer · answered Mar 09 '16 at 21:39

Try below query:

DECLARE TABLE #temp(name nvarchar(MAX), email nvarchar(MAX), ranking int)
DECLARE @qu NVARCHAR(MAX), @pcol NVARCHAR(MAX)


INSERT INTO #temp
SELECT  
    A.name AS name,
    C.email AS email,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY email ASC) AS ranking
FROM Agency A LEFT JOIN Contact C ON A.id=C.agency_id


SELECT   
 @pcol= COALESCE(@pcol + ',','') + ContactNumber 
FROM
 (
  SELECT 
        DISTINCT N'Contact'+ CAST (ranking AS NVARCHAR(25)) AS ContactNumber 
  FROM #temp
  ) A

SET @qu=N'SELECT Name,'+ @pcol +  
N'FROM 
    (
      SELECT 
            Name,
            N''Contact''+ CAST (ranking AS NVARCHAR(25)) AS ContactNumber, 
            email 
      FROM #temp
    )S
  PIVOT
  (MAX(email) FOR ContactNumber IN ('+@pcol +N')) AS piv'
EXEC sp_executesql @qu
DROP TABLE #temp

score 0 · Answer 2 · answered Mar 10 '16 at 00:40

If you always want to show 3 columns and you will not have more than that, you can use CTE with window function instead of creating a dynamic query

;WITH C AS(
    SELECT RANK() OVER (PARTITION BY name ORDER BY Email) AS [Rank]
           ,b.id, b.name, Email
    FROM @Contact AS a
    INNER JOIN @Agency AS b ON a.agency_id = b.id
)
SELECT name
    ,MIN(CASE C.[Rank] WHEN 1 THEN Email END) AS [Email1]
    ,MIN(CASE C.[Rank] WHEN 2 THEN Email END) AS [Email2]
    ,MIN(CASE C.[Rank] WHEN 3 THEN Email END) AS [Email3]
FROM C
GROUP BY name

Create a new column for each joined record

2 Answers2