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 | email1@email.com 1 | email2@email.com 1 | email3@email.com 2 | email4@email.com 2 | email5@email.com 3 | email6@email.com 4 | email7@email.com 4 | email8@email.com
I want the table to look like this(when all are included):
Number | Email1 | Email2 | Email3 --------------------------------------------------------------- 1 | email1@email.com | email2@email.com | email3@email.com 2 | email4@email.com | email5@email.com | 3 | email6@email.com | | 4 | email7@email.com | email8@email.com |
If Number 1 wasn’t included it would look like:
Number | Email1 | Email2 -------------------------------------------- 2 | email4@email.com | email5@email.com 3 | email6@email.com | 4 | email7@email.com | email8@email.com
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,'email1@email.com')
,(1,'email2@email.com')
,(1,'email3@email.com')
,(2,'email4@email.com')
,(2,'email5@email.com')
,(3,'email6@email.com')
,(4,'email7@email.com')
,(4,'email8@email.com')
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;
Advertisement
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,'email1@email.com')
,(1,'email2@email.com')
,(1,'email3@email.com')
,(2,'email4@email.com')
,(2,'email5@email.com')
,(3,'email6@email.com')
,(4,'email7@email.com')
,(4,'email8@email.com')
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');