Create a new column for each attached record

advertisements

This question already has an answer here:

  • SQL Server dynamic PIVOT query? 6 answers

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           | [email protected] | [email protected] | [email protected] |
-----------------------------------------------------------------

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


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 ('[email protected] +N')) AS piv'
EXEC sp_executesql @qu
DROP TABLE #temp