I have a situation where i have to concatenate rows based of group of value or without group, i don't want to use tsql. can any one let me know wether we have any function for it.
eg train_no direction 123 south-east 124 south-east 125 south-east 126 south-east 131 south-west 132 south-west 156 east
I want result as below train,no direction (123,124,125,126) South-East (131,132) South-West (156) esst
SELECT
STUFF(trains,1,1,'') AS trains,
direction
FROM
(SELECT DISTINCT direction FROM TheTable) a
CROSS APPLY
(
SELECT ',' + CAST( train_no AS varchar(32))
FROM TheTable b
WHERE b.direction = a.direction
FOR XML PATH('')
) c(trains)
concatenation of rows
Posted - 02/04/2013 : 06:03:41
