TSQL: Multiple rows into single column in query result

Question

I feel I am missing a simple T-SQL answer to this question. I have a Measurements table, and an Activity table related by MeasurementID column, and there are at least 3 activities (sometimes more) related to a single measurement. How do I construct a query such that the output would look like this:

Measurement ID         Activities
      1                 Running:Walking:Eating
      2                 Walking:Eating:Sleeping

I would also be satisfied if the output looked like this:

Measurement ID       Activity1    Activity2     Activity3
      1              Running      Walking       Eating

Is there a simple single query way to do this, or must I use (shudder) cursors to do the trick?

Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Oct 20 '15 at 14:37
@juergend the table doesn't hold multiple values in a column, he just wants a query that will display that result for some reason. — Tab Alleman, Oct 20 '15 at 14:38
For the first requested output, here is a duplicate: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Tab Alleman, Oct 20 '15 at 14:39
@TabAlleman Thanks for the two duplicates, I tried to search but missed those two. The second was very useful, close to what I needed, and what I need in the future. Thx. — user3772397, Oct 21 '15 at 12:48

score 4 · Accepted Answer · answered Oct 20 '15 at 14:41

4

Unfortunately, there is no GROUP_CONCAT() in T-SQL. There is a trick to simulate it, though:

SELECT
  MeasurmentID,
  Activities = REPLACE((SELECT Activity AS [data()]
                        FROM MeasurmentActivities
                        WHERE MeasurmentID = ma.MeasurmentID
                        FOR xml path('')), ' ', ':')
FROM
  MeasurmentActivities AS ma
GROUP BY
  MeasurmentID

answered Oct 20 '15 at 14:41

Dmytro Shevchenko

33,431
6
51
67

This approach works for me. I can expand on the concept for sure. Thanks! – user3772397 Oct 20 '15 at 18:17
1

Thanks this worked for me. I tweaked it slightly as the data I was trying to concatenate included spaces. My solution was to add a pipe to the SELECT and then replace that `Activities = REPLACE((SELECT Activity AS [data()], '|' FROM MeasurmentActivities WHERE MeasurmentID = ma.MeasurmentID FOR xml path('')), '|', ':')` – TimSmith-Aardwolf Oct 28 '19 at 12:03
worked great. I added [data()] and it removed xml syntax – Golden Lion Mar 20 '20 at 16:40

score 0 · Answer 2 · answered Oct 20 '15 at 14:46

If you know in advance the number of activies by measurement, you can try the PIVOT operator.

Otherwise there is no easy way to do it.

If you can, I would suggest you to "horizontalize" the rows on your application-end.

If that is not an option, the only way I suppose would work in Transact-SQL is to convert your result in XML output and tweak it using XPATH queries.

TSQL: Multiple rows into single column in query result

2 Answers2