pivot sql concatenate

SQL Pivot

andy

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.

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

--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
select @ColumnName

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.