In reviewing SQL Pivot for a project I did discover a new way I was not familiar with to concatenate rows to a single column.
--Create Temporary Table #CourseSales CREATE TABLE #CourseSales (Course VARCHAR(50),Year INT,Earning MONEY) GO --Populate Sample records INSERT INTO #CourseSales VALUES('.NET',2012,10000) INSERT INTO #CourseSales VALUES('Java',2012,20000) INSERT INTO #CourseSales VALUES('.NET',2012,5000) INSERT INTO #CourseSales VALUES('.NET',2013,48000) INSERT INTO #CourseSales VALUES('Java',2013,30000) GO
--INSERT INTO CourseSales VALUES('Sql Server',2013,15000)
DECLARE @ColumnName AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Course) FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
So the magic comes in setting the set to a single variable and as it adds it can tell there is data already there and simply concatenates each item in the row. I have done with with making something an XML variable and then remove out XML tags to replace with a comma. This is much simpler.