how to split comma separated values in sql without function

Question

 DECLARE @list VARCHAR(MAX)

SELECT @list = COALESCE(@listStr+',' , '') + columnname

FROM tablename

SELECT @list