concatenation of rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

concatenation of rows

Author

Topic

guddu_12
Starting Member

1 Post

Posted - 2013-02-04 : 06:03:41

Hi ,

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

Please help

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 06:11:07

[code]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)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources