SELECT
	a.*,
	LTRIM(b.data) AS SubscriberDetails
FROM
(
	SELECT DISTINCT 
		SubscriberId,
		SubscriberTitle,
		Email,
		FirstName,
		LastName
	FROM
		Tbl
) AS a
CROSS APPLY
(
	SELECT
		' ' + b.ArticleTitle + ' ' + b.CategoryName
	FROM
		Tbl b
	WHERE
		b.SubscriberId = a.SubscriberId
	ORDER BY
		b.PK
	FOR XML PATH('')
) b(data);

DECLARE @Foo TABLE 
(
    Pk INT, 
    SubscriberId INT, 
    SubscriberTitle VARCHAR(4), 
    Email VARCHAR(255),  
    FirstName VARCHAR(50), 
    LastName VARCHAR(50),
    ArticleTitle VARCHAR(2000),
    CategoryName VARCHAR(255)
)

INSERT @Foo VALUES
    (1, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article ONE', 'News Releases'),
    (2, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article TWO', 'News Releases'),
    (3, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article THREE', 'News Releases'),
    (4, 2, 'Mr.', 'test@test.com', 'Larry', 'Mills', 'This is an article ONE', 'News Releases')



SELECT DISTINCT	
	Source.SubscriberId,
	STUFF((SELECT DISTINCT TOP 100 PERCENT ' ' + T1.ArticleTitle + ' ' + T1.Categoryname FROM @Foo AS T1 WHERE T1.SubscriberId = Source.SubscriberId ORDER BY  ' ' + T1.ArticleTitle + ' ' + T1.Categoryname FOR XML PATH('')), 1, 1, '') AS SubscriberDetails
FROM		
	@Foo AS Source
ORDER BY	
	Source.SubscriberId

quote:

---

Originally posted by James K

This is how I would go at it:

SELECT
	a.*,
	LTRIM(b.data) AS SubscriberDetails
FROM
(
	SELECT DISTINCT 
		SubscriberId,
		SubscriberTitle,
		Email,
		FirstName,
		LastName
	FROM
		Tbl
) AS a
CROSS APPLY
(
	SELECT
		' ' + b.ArticleTitle + ' ' + b.CategoryName
	FROM
		Tbl b
	WHERE
		b.SubscriberId = a.SubscriberId
	ORDER BY
		b.PK
	FOR XML PATH('')
) b(data);

---

--I need the rows for each unique SubscriberId combined into a NEW table, so in this instance it will be a 2 row result table since there are only two unique SubscriberId's. 
--The format of the new table is simply to reuse columns 2-4 (i.e not Pk) and just concatenate ALL the "ArticleTitle" and "CategoryName" columns for each SubscriberId's row into a new column: "SubscriberDetails".

DECLARE @Foo TABLE 
(
    Pk INT, 
    SubscriberId INT, 
    SubscriberTitle VARCHAR(4), 
    Email VARCHAR(255),  
    FirstName VARCHAR(50), 
    LastName VARCHAR(50),
    ArticleTitle VARCHAR(2000),
    CategoryName VARCHAR(255)
)

INSERT @Foo VALUES
    (1, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article ONE', 'News Releases'),
    (2, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article TWO', 'News Releases'),
    (3, 1, 'Mr.', 'test@test.com', 'Rich', 'Smith', 'This is article THREE', 'Industry News & Market Reports'),
    (4, 2, 'Mr.', 'test@test.com', 'Larry', 'Mills', 'This is an article ONE', 'News Releases')

SELECT
	a.*,
	LTRIM(b.data) AS SubscriberDetails
FROM
(
	SELECT DISTINCT 
		SubscriberId,
		SubscriberTitle,
		Email,
		FirstName,
		LastName
	FROM
		@Foo
) AS a
CROSS APPLY
(
	SELECT
		' ' + ArticleTitle + ' ' + CategoryName
	FROM
		@Foo
	WHERE
		SubscriberId = a.SubscriberId
	ORDER BY
		PK
	FOR XML PATH('')
) b(data);