Hi,

I am trying to combine information from two or more records into one and I am
completely stuck on a solution for my problem so I hope there is someone out
there who can help me.

My table looks like this:
ID  -  DayNr  -  Transportation  -  TransOrder  -  Route
25  -    1      -      Car             -      1            -  Text A
25  -    1      -      Train           -      1            -  Text B
25  -    1      -      Train           -      2            -  Text C
25  -    7      -      Train           -      1            -  Text D
25  -    7      -      Train           -      2            -  Text E

I want to combine all Route - information belonging to the same combination
of ID & DayNr & Transportation into one new record. The result should look
like:

Column 1   -  Column 2
25/1/Car   -  Text A
25/1/Train -  TextB;TextC
25/7/Train -  TextD;TextE

I have tried Coalesce-statements and Cursor-solutions but until now
everything I tried didn't work. Ideas anyone?

Thanks.
RMG

P.S. ID is not my primary key and doesn't have to be unique

How To incorporate table-valued function in Common Table Expressio
Getting 10 Records the Fastest Way Possible // ROWLOCK, UPDLOCK, READPAST
Using ranking functions in a subquery to feed a CLR function values in a specified order.
Challenging Query - Please Help
JOIN Question...