Concatenate column values from multiple rows into a single column in SQL Server

I have created a simple table to demonstrate how to concatenate the values from multiple rows in to one column.

CREATE TABLE #tblProduct (product_Id INT IDENTITY(1,1), product_name VARCHAR(10))

Insert some sample records in to this temporary table

INSERT INTO #tblProduct (product_name) VALUES('AAA')
INSERT INTO #tblProduct (product_name) VALUES('BBB')
INSERT INTO #tblProduct (product_name) VALUES('CCC')
INSERT INTO #tblProduct (product_name) VALUES('DDD')
INSERT INTO #tblProduct (product_name) VALUES('EEE')
INSERT INTO #tblProduct (product_name) VALUES('FFF')
INSERT INTO #tblProduct (product_name) VALUES('GGG')

Once you have populated the table with data, Run this query to get the row values in to one column.

DECLARE @productarr VARCHAR(8000)
SET @productarr = '' 
SELECT @productarr = @productarr + '"' + CAST(product_Id AS VARCHAR) + '",' 
FROM #TBLPRODUCT ORDER BY product_Id 

SELECT LEFT(@productarr,LEN(@productarr)-1) 

DROP TABLE #tblProduct