0
DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ', ', '') + DriversTransportDetails.DriverName from DriversTransportDetails 

select  trID, @combinedString as C 
from DriversTransportDetails 
group by DriversTransportDetails.trID

This is my code, I have table called DriversTransportDetails which contains columns trID (int) and driverName (nvarchar)

trID is not unique so trID can be in multiple rows depends on drivers, ex :

TrID  DriverName
1     Tony
1     Rony
2     Jeorge
3     Jim

I want to COALESCE driver name rows into one row, desired result :

TrID   C
1      Tony, Rony
2      Jeorge
3      Jim

The problem is the result is not correct, it shows all drivers combined into column c, like this :

TrID   C
1      Tony, Rony, Jeorge, Jim
2      Tony, Rony, Jeorge, Jim
3      Tony, Rony, Jeorge, Jim

What's the problem ?

Thank you very much :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AymAn AbuOmar
  • 403
  • 1
  • 5
  • 16
  • you could create scalar valued function to return coalesced string based on unique TrId like: `select ... dbo.GetListOfDrivers(TrId) as C` – Milen Dec 09 '13 at 17:42
  • it is at the same direction, but i am using sql 2008 and COALESCE function. – AymAn AbuOmar Dec 09 '13 at 17:42
  • @Milen I would not bother using a function for this - you can do it inline quite simply; a function does nothing but save a few characters at the cost of significant overhead. – Aaron Bertrand Dec 09 '13 at 17:43
  • understood @AaronBertrand – Milen Dec 09 '13 at 17:44
  • for those who marked this as duplicated question, where is the answer ?! – AymAn AbuOmar Dec 09 '13 at 17:46
  • 2
    @AymAnAbuOmar - Use `XML PATH` per the dupe. Using `Coalesce`/ aggregate concatenation [is not guaranteed to work](http://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior/15163136#15163136) and can only be done in a correlated way if you create a scalar UDF. – Martin Smith Dec 09 '13 at 17:46
  • 2
    You'll have to change it a bit, of course, but [you get to the answer(s) by scrolling down](http://stackoverflow.com/a/451441/61305). – Aaron Bertrand Dec 09 '13 at 17:48
  • thank you very much, but honestly i do not know what are you talking about (XML PATH),anyway, i am trying to do that by COALESCE function because i am planning to use it in Crystal Reports, is XML PATH available also with CR ? – AymAn AbuOmar Dec 09 '13 at 17:50
  • No idea. Can't you run arbitrary SQL? [SQL Fiddle](http://sqlfiddle.com/#!3/179c1/1) – Martin Smith Dec 09 '13 at 18:00

0 Answers0