SQL Pivot

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
(Course VARCHAR(50),Year INT,Earning MONEY)
--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)
--INSERT INTO CourseSales VALUES('Sql Server',2013,15000)
--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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>