Concatenate Rows for one column SQL

Question

I am using following query to extract appointment data:

SELECT app.subject, AL.locationName
FROM FilteredAppointment app
INNER JOIN appointmentlocation AL ON app.activityid = AL.appointment
WHERE app.scheduledstart='2013-07-06 15:00:00.000'

The output is as follows with 2 rows (same appointment with two different locations):

enter image description here

How can i modify this query to display only one row with two locations concatenated with comma like below:

Column1: (MZN; OTV)*...
Column2: Room1,Room2

Thanks

it is not duplicate to that question as I needed to populate the column values from different rows and same column. The post your suggested, the values are coming from other columns. — Azeem, Aug 01 '13 at 11:44

score 5 · Accepted Answer · edited May 23 '17 at 12:02

what you need is SQL Join and concatenate rows, there are many questions on SO about it. There's no easy way to do this in SQL Server, but here some tricks:

Concatenate by using select for xml

select
    app.subject,
    stuff(
       (
           select ', ' + AL.locationName
           from appointmentlocation as AL
           where AL.appointment = app.activityid
           for xml path(''), type
       ).value('.', 'nvarchar(max)')
    , 1, 2, '') 
from FilteredAppointment as app
where app.scheduledstart='2013-07-06 15:00:00.000'

if you have only one record from FilteredAppointment to concatenate, you could use aggregating into variable:

declare @locations nvarchar(max), @activityid int

select @activityid = ???

select @locations = isnull(@locations + ', ', '') + AL.locationName
from appointmentlocation as AL
where AL.appointment = @activityid

print @locations

score 2 · Answer 2 · answered Aug 01 '13 at 10:10

This example will help you .. or wait until I makequery for you

  USE app.subject,
    SELECT      AL.locationName AS [Loc],
                STUFF((    SELECT ',' + SUB.Name AS [text()]
                            – Add a comma (,) before each value
                            FROM appointmentlocation AL
                            WHERE
                            app.activityid = AL.appointment

                            FOR XML PATH('') – Select it as XML
                            ), 1, 1, '' )
                            – This is done to remove the first character (,)
                            – from the result
                AS [Sub Categories]
    FROM  FilteredAppointment app

thanks but i guess answer given by Roman Pekar is working... — Azeem, Aug 01 '13 at 11:46

score 0 · Answer 3 · answered Aug 01 '13 at 10:08

0

SELECT app.subject, GROUP_CONCAT(AL.locationName, ', ') AS LocationName
FROM FilteredAppointment app
INNER JOIN appointmentlocation AL ON app.activityid = AL.appointment
WHERE app.scheduledstart='2013-07-06 15:00:00.000'
GROUP BY app.subject

Untested

answered Aug 01 '13 at 10:08

Dale

10,384
21
34

GROUP_CONCAT is nice, but MySQL specific. The post is tagged MS SQL. – jpw Aug 01 '13 at 10:09
Ah my bad, wasn't aware it was specific to mysql! – Dale Aug 01 '13 at 10:09
@jpw : OP wants in SQL Server. – Romesh Aug 01 '13 at 10:11
indeed but you can find a workaround here : [Simulating group_concat MySQL function in Microsoft SQL Server 2005](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Marc Aug 01 '13 at 10:15
'GROUP_CONCAT' is not a recognized built-in function name in SQL Server – Azeem Aug 01 '13 at 10:18

score 0 · Answer 4 · edited Aug 01 '13 at 10:12

0

I can't test it right now but I think this might do the work

select subject, 
       group_concat(name separator ',') Newcolumn
  from table
 group by subject

edited Aug 01 '13 at 10:12

Romesh

2,291
3
24
47

answered Aug 01 '13 at 10:09

Marc

2,631
1
12
13

this is for MySQL not for SQL Server – Roman Pekar Aug 01 '13 at 10:14
Yes I didn't know that but I gave a workaround for msSQL in @Dale post – Marc Aug 01 '13 at 10:17

Concatenate Rows for one column SQL

4 Answers4

Linked