Concatenate multiple rows in one row

Question

I've been trying to do this until I can't figure it out how,

I just wanted to concatenate multiple rows into one row and make it the value of the final column query. To illustrate;

Initial Query:

       ColumnOne
       ---------
 Row1 | 1 - One
 Row2 | 2 - Two
 Row3 | 3 - Three

I want to do something like,

 1 - One, 2 - Two, 3 - Three

The Final Result should like these:

Name | Age | Text
-----------------
Paul | 12  | 1 - One, 2 - Two, 3 - Three
John | 34  | 1 - One, 2 - Two, 3 - Three
Alex | 15  | 1 - One, 2 - Two, 3 - Three

I'll be using the Text Column as a description for an RDL field

Anyone pls.?

Is there any relation between `name` `age` to this `columnOne` values — pratik garg, May 22 '13 at 06:28
There is none right now, but eventually the SELECT stmt that is use to query would be JOINed to another table later on, would that have major effect on the solution? — mirageservo, May 22 '13 at 06:30
The For XML clause mentioned by Tobias Schulte is the way I typically get this done. This will return an XML fielf for you with tags and whatnot around each data element. After getting this XML back, I usually use the RDL code behind to strip the XML for some calculated field on the dataset. — Mark W, Jun 10 '13 at 18:15

score 4 · Accepted Answer · answered May 22 '13 at 06:30

Assuming your tables are named tab1 and tab2 and theres a column ID that you use for the relation, this should do the job:

SELECT t2.Name, t2.Age,
    (SELECT tab1_inner.ColumnOne + ','
        FROM tab1 tab1_inner
        INNER JOIN tab2 tab2_inner
        ON tab1_inner.ID = tab2_inner.ID
        WHERE t2.ID = tab1_inner.ID
        FOR XML PATH('')
    ) AS Text
FROM tab2 t2

Concatenate multiple rows in one row

1 Answers1