sql server - Select multi value from another table and put in one string split by - -

i have 2 tables in ms sql server.

table 1

id   name   ------------- 10   series       11   movie       12   music       13   other      

table 2

id    idcatg    value   --------------------------- 1       10          hannibal 2       10          blacklist 3       10          poi 4       11          hitman 5       11          saw 6       11          spider man 7       12          taylor swift 8       12          britney spears 

i want select idcatg in table 2 , create new column in table 1 this:

idcatg     name        value -------------------------------------------- 10         series      hannibal-blacklist-poi 11         movie       hitman-saw-spider man 12         music       taylor swift-britney spears 

how can view?

you can using stuff:

select t21.idcatg, t1.name,       [value] = stuff((           select '-' + t22.[value]           table2 t22           t21.idcatg = t22.idcatg           xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') table2 t21 join       table1 t1 on t1.id=t21.idcatg group t21.idcatg,t1.name 

result:

idcatg  name    value --------------------------------------------- 10      series  hannibal-blacklist-poi 11      movie   hitman-saw-spider man 12      music   taylor swift-britney spears 

sample result in sql fiddle

edit:

when type of value int, can cast varchar:

  [value] = stuff((       select '-' + cast(t22.[value] varchar(max))