0

I'm having a bit of a problem that I just can't seem to get my head round. I've got a table that I'm trying to extract some data from. Below is an example of the data.

Id  DevId     Route
1   1         1
2   1         2
3   1         3
4   1         4
5   2         1
6   2         2

What I want the result to look like would be:

DevId     Route
1         1234
2         12

Don't know if I've been looking at it for too long or something, but I just can't work out how to do it.

Any help or suggestions would be greatly appreciated.

Thanks

Alex

denimknight
  • 301
  • 2
  • 9
  • 22

1 Answers1

0

Create table ##tmp (id int, name int) insert into ##tmp values(1,1) insert into ##tmp values(1,2) insert into ##tmp values(1,3) insert into ##tmp values(2,1) insert into ##tmp values(2,2)

Select id, (Select cast (name as varchar(4)) from ##tmp t2 where t1.id = t2.id for xml path('') ) from ##tmp t1 group by id

drop table ##tmp

arunbabu
  • 279
  • 3
  • 15