TexCopy Sql Server 2005

In one to many relationships of two tables, sometimes we need to write a query which can return comma separated list of all child values for each parent.
Here are two different methods by which you can easily create a Comma Separated List, the first runs from sql 2000 onwards and the second on is for SQL 2005 onwards.

USE tempdb
GO
CREATE TABLE [dbo].[student](
	StudentID INT IDENTITY,
	Name   varchar(50) ,
) ON [PRIMARY]
GO
INSERT INTO Student (name)
SELECT 'One' 
UNION ALL
SELECT 'Two'
UNION ALL 
SELECT 'Three'
UNION ALL
SELECT 'Four'

 

SQL Server 2000 Onwards Version

DECLARE @Csv VARCHAR(1000) 
SELECT @csv = COALESCE(@Csv+',', '')+NAME
FROM [dbo].[student]
SELECT @Csv

SQL Server 2005 Onwards version

SELECT CASE WHEN LEN(name) > 0 THEN LEFT(name, LEN(name)-1) ELSE '' END AS name 
FROM (
	SELECT p.name +',' 
	FROM dbo.student p
	FOR XML PATH('')
)tmp (name)