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!

Don't do this to yourself. What you are trying to do is shove a bunch of values into a single column. This violates 1NF and is a serious pain to work with. If you do this, you will have to parse this column every time you need to query it. Instead you should normalize your data. — Sean Lange, Aug 19 '15 at 14:55
`concat(Field1, ' - ', field2, ' - ', field3)` but why this seems like it could cause trouble downstream — xQbert, Aug 19 '15 at 14:55
I couldn't understand this, so you want to transpose column values to rows, or what? — Helio, Aug 19 '15 at 15:55
Can you check this if it's beneficial: http://stackoverflow.com/a/13377114/673707 — Helio, Aug 19 '15 at 15:57

score 2 · Accepted Answer · answered Aug 19 '15 at 14:55

2

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

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

answered Aug 19 '15 at 14:55

LaVomit

492
5
15

Perfect, this allowed me to split it at those instances, thanks! – Marco Aug 19 '15 at 19:19

score 0 · Answer 2 · answered Aug 19 '15 at 15:07

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),'');

How can I append a column in SQL?

2 Answers2