How can I append a column in SQL?

Question

I've tried different things in SQL Server 2012 to append the columns. CONCAT merges the columns look like this: catdogparrot whereas I want it be in a list like:

 cat
 dog
 parrot

I've also tried the + in SQL, giving me the same result. I saw '||' as well, but for some reason it says wrong syntax at the second pipe. Is there a way to append a column to a new one? Or would I have to create a new column from the multiple columns? These columns are in the same table. Any suggestions are advice are greatly appreciated, thanks!

Answer 1

I'm not sure what you are trying to do, but you could try to use

CONCAT(' - ', `column1`, ' - ', `column2`, ' - ', ... `column999`)

Answer 2

Eventually this could help you: You can set the separator to any sequence, even to CHAR(13)+CHAR(10), which is a windows line break.

DECLARE @dummyTbl TABLE(animal VARCHAR(10));
INSERT INTO @dummyTbl
VALUES('cat'),('dog'),('parrots');

DECLARE @separator VARCHAR(5)= ' / ';

SELECT STUFF(
(
    SELECT @separator + animal 
    FROM @dummyTbl AS dt
    FOR XML PATH(''),TYPE
).value('.','varchar(max)'),1,LEN(@separator),'');