sql - Which is the best way to form the string value using column from a Table with rows having same ID?

Question

I am using SQL Server 2008.

This are my tables.

Table A

 Aid int pk
 col2
 col3
 XYZID int

Table B

 Bid int pk
 XYZID int
 Col2
 Col3 
 Col4
 seq -- (Sequence )

Table B will be like this

seq   col2    |XYZID|Bid |col3| col4 
===============================================
  1   foo     | 1   |  1 | 12 | wqw
  3   bar     | 1   | 10 | 77 | kikk
  2   foobar  | 1   |  2 |  w | ed
  1   barfoo  | 2   |  4 |  e | dwe
  2   asdsad  | 2   |  5 |  e | e 

Table A is the main table and based on the XYZID in A

I need to generate a string value using col from TableB with XYZID and Seq .

I need to generate a string value using col from TableB with XYZID and Seq .

For eg: xyzid= 1

I expected : foo-12-wqw#foobar-w-ed#bar-77-kikk

based on Sequence foo-1,foobar-2,bar-3

Answer 1

For XYZID = 1.

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB
              where XYZID = 1
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

For all rows in TableA:

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB as B
              where A.XYZID = B.XYZID
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
from TableA as A