Thank you so much this is correct. You saved me hours from excel.
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;