Phil Parkin (8/18/2016)

---

shani19831 (8/18/2016)

---

Thank you for the reply.

Yes the result is not really the same but i can work from excel to change it. something I would love to avoid..

I cant get the :: correct, as it appends : at the beginning of the record and i don't know how to remove it.

Try this

SELECT t1.ID

, STUFF((SELECT '::' + CAST(t2.MOBILE_NUMBER AS VARCHAR(10))

FROM #MTCBEN t2

WHERE t2.ID = t1.ID

FOR

XML PATH('')

), 1, 2, '') AS mobile

, STUFF((SELECT '::' + CAST(t3.AMOUNT AS VARCHAR(10))

FROM #MTCBEN t3

WHERE t3.ID = t1.ID

FOR

XML PATH('')

), 1, 2, '') AS amount

, STUFF((SELECT '::' + CAST(t4.CURRENCY AS VARCHAR(10))

FROM #MTCBEN t4

WHERE t4.ID = t1.ID

FOR

XML PATH('')

), 1, 2, '') AS currency

, STUFF((SELECT '::' + CAST(t5.PROCESSING_DATE AS VARCHAR(10))

FROM #MTCBEN t5

WHERE t5.ID = t1.ID

FOR

XML PATH('')

), 1, 2, '') AS 'Date'

FROM #MTCBEN t1

GROUP BY t1.ID;