Dynamic Pivot of Email Addresses

I have tried to research this, and I am unable to find something quite like it. I have a table that may have entries added many times over as well as deleted. I have no idea how many columns I will need, therefore I need a Dynamic Pivot. All the examples I see use a windows function, but I am pivoting email addresses.

The Table would look something like this:

Number | Email
--------------
1      | [e-mail]
1      | [e-mail]
1      | [e-mail]
2      | [e-mail]
2      | [e-mail]
3      | [e-mail]
4      | [e-mail]
4      | [e-mail]

I want the table to look like this(when all are included):

Number | Email1           | Email2           | Email3           
---------------------------------------------------------------
1      | [e-mail] | [e-mail] | [e-mail]
2      | [e-mail] | [e-mail] |
3      | [e-mail] |                  |
4      | [e-mail] | [e-mail] |

If Number 1 wasn’t included it would look like:

Number | Email1           | Email2                     
--------------------------------------------
2      | [e-mail] | [e-mail] 
3      | [e-mail] |
4      | [e-mail] | [e-mail]

Thanks for the help!

Here is code to create a mock table:

CREATE TABLE #table
(number INT, email VARCHAR(30))

INSERT INTO #table (number, email)
VALUES  (1,'[e-mail]')
       ,(1,'[e-mail]')
       ,(1,'[e-mail]')
       ,(2,'[e-mail]')
       ,(2,'[e-mail]')
       ,(3,'[e-mail]')
       ,(4,'[e-mail]')
       ,(4,'[e-mail]')

This is similar to what I have tried, I used count to try and just make it work, but was unable.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Number)
  FROM (SELECT p.Number FROM #table AS p
  GROUP BY p.Name) AS x;

SELECT @columns

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.number, p.email
   FROM #test p
) AS j
PIVOT
(
  Count(email) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Answer

You first need to create a RNO by partition over Number. The following query should do what you want:

CREATE TABLE #table (Number INT, Email VARCHAR(30))
INSERT INTO #table (Number, Email)
VALUES  (1,'[e-mail]')
       ,(1,'[e-mail]')
       ,(1,'[e-mail]')
       ,(2,'[e-mail]')
       ,(2,'[e-mail]')
       ,(3,'[e-mail]')
       ,(4,'[e-mail]')
       ,(4,'[e-mail]')

SELECT *, ROW_NUMBER() OVER (PARTITION BY Number ORDER BY Number, Email) AS [RNO] INTO #temp FROM #table

DECLARE @DynamicCols NVARCHAR(MAX) = '';
DECLARE @pvt NVARCHAR(MAX) = '';

SET @pvt = STUFF(
  (SELECT DISTINCT N', ' + QUOTENAME([RNO]) FROM #temp FOR XML PATH('')),1,2,N'')

SET @DynamicCols = STUFF(
  (SELECT DISTINCT N', ' + QUOTENAME([RNO]) + ' AS '+ QUOTENAME('Email' + CAST([RNO] AS VARCHAR(MAX))) FROM #temp FOR XML PATH('')),1,2,N'')


EXEC (N'SELECT [Number],'+ @DynamicCols+'
            FROM #temp tmp
        PIVOT (MAX([Email]) FOR RNO IN('+@pvt+')) AS PIV');