-- List of all Production.ProductSubcategories that a contact has ordered:
-- Choose contact that has ordered the most items
DECLARE @ContactId INT

SELECT @ContactId = ContactId
FROM Person.Contact
WHERE ContactId = (SELECT TOP(1) ContactId FROM Sales.SalesOrderHeader GROUP BY ContactId ORDER BY COUNT(*) DESC);

WITH ColumnToPivot ([data()]) AS (
        SELECT TOP 100 PERCENT ps.Name + N', ' AS ColumnYouWantToMakeACSVList_PlusAComma
        FROM Production.ProductSubcategory ps
        WHERE EXISTS (
            SELECT *
            FROM Production.Product p JOIN Sales.SalesOrderDetail od
                ON od.ProductId = p.ProductId
            JOIN Sales.SalesOrderHeader o
            ON o.SalesOrderId = od.SalesOrderId
            WHERE o.ContactId = @ContactId
                AND p.ProductSubcategoryID = ps.ProductSubcategoryID
            )
        ORDER BY ColumnYouWantToMakeACSVList_PlusAComma -- better to just order by ps.Name but, for example purposes, I include this
        FOR XML PATH(''), TYPE
    )
    , XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
    )
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

-- Alternate approach:
DECLARE @Title VARCHAR(128)
SET @Title ='POP3 and IMAP4 in Exchange 2003';

DECLARE @ColumnList NVARCHAR(MAX) 
SET @ColumnList = ''
SELECT @ColumnList = @ColumnList + N', ' + CAST(vd.SubscriberId AS NVARCHAR)
    FROM Videos v JOIN dbo.VideosDownloadedBySubscriber vd
        ON v.VideoId = vd.VideoId
WHERE v.Title = @Title

SELECT NULLIF(SUBSTRING(@ColumnList, 3, 1024), '')

-- Another:
SELECT  STUFF((SELECT ', ' + name FROM sys.databases ORDER BY name FOR XML PATH('')), 1, 1, '')