big concat.sql

USE tempdb
GO

DROP TABLE IF EXISTS dbo.[UMG sent 2016]
GO

CREATE TABLE dbo.[UMG sent 2016] (
	rowId			INT IDENTITY UNIQUE,
	email			VARCHAR(100) NOT NULL,
	prject_name		VARCHAR(20) NOT NULL,

	PRIMARY KEY ( email, prject_name )
	)
GO


-- Create some dummy data
;WITH cte AS (
SELECT TOP 200000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
	CROSS JOIN master.sys.columns c2
	CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.[UMG sent 2016] ( email, prject_name )
SELECT 'email' + FORMAT( a.rn, '00000@email.com' ), FORMAT( b.rn, 'shift0' )
FROM cte a
	CROSS JOIN ( SELECT TOP 26 * FROM cte ) b
WHERE b.rn <= a.rn
  AND b.rn  <= ( a.rn % 26 ) + 1
ORDER BY 1, 2
GO

-- Check all emails exist?
SELECT COUNT(*) FROM [UMG sent 2016]

SELECT email, COUNT(*)
FROM dbo.[UMG sent 2016]
GROUP BY email
ORDER BY email
GO



-- Create the working table ...
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT ROW_NUMBER() OVER( PARTITION BY email ORDER BY prject_name ) rowId, email, CAST( prject_name AS VARCHAR(500 ) ) prject_name
INTO #tmp
FROM dbo.[UMG sent 2016]
GO

SELECT TOP 100 'before' s, *
FROM #tmp
ORDER BY email


-- Loop through appending the projects
DECLARE @n INT = 1

WHILE @@ROWCOUNT != 0
BEGIN

	IF @n > 99 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END	-- Loop safety
	SET @n += 1

	UPDATE t
	SET t.prject_name = CONCAT( t.prject_name, ', ', s.prject_name )
	FROM #tmp t
		INNER JOIN #tmp s ON t.email = s.email
	WHERE t.rowId = 1
	  AND s.rowId = @n

END
GO

SELECT TOP 100 'after' s, *
FROM #tmp
WHERE rowId = 1
ORDER BY email




SELECT DISTINCT o.prject_name, names= STUFF((
    SELECT ', ' + prject_name 
    FROM dbo.[UMG sent 2016] AS a
    WHERE a.prject_name = o.prject_name
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.[UMG sent 2016] AS o;