0

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 to select by IDCatg in Table 2 and create a new column in Table 1 like this:

IDCatg     Name        Value
--------------------------------------------
10         Series      Hannibal-Blacklist-POI
11         Movie       Hitman-SAW-Spider man
12         Music       taylor swift-britney spears

How can I do this by view?

mosyflasher
  • 489
  • 1
  • 6
  • 16
  • possible duplicate of [Concatenate row values T-SQL](http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql) – James Z Jul 11 '15 at 09:52

1 Answers1

2

You can do it using STUFF:

SELECT T21.IDCatg, T1.Name,
      [Value] = STUFF((
          SELECT '-' + T22.[Value]
          FROM Table2 T22
          WHERE T21.IDCatg = T22.IDCatg
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table2 T21 JOIN 
     Table1 T1 ON T1.ID=T21.IDCatg
GROUP BY 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 the type of Value is int, you can cast it to varchar:

  [Value] = STUFF((
      SELECT '-' + CAST(T22.[Value] AS Varchar(MAX))
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Thank you , it works , but if my Value type is int , i get this error: conversion failed when converting the varchar value ',' to data type int – mosyflasher Jul 11 '15 at 07:36
  • generally when my value type is int , '-' doesn't work ! what i have to do – mosyflasher Jul 11 '15 at 07:40
  • another question pls! if i want to JOIN value to another table and replace value by other field in table 3 , how can i do that ? – mosyflasher Jul 11 '15 at 08:05
  • @mosyflasher: Just replace `Table2` with the name of the new table and replace `Value` with the new field name. – Raging Bull Jul 11 '15 at 08:09
  • i don't think this is true . because i need 3 table here . i mean this select that we did is true , now i want replace only value by joining to table 3 . example: Hannibal-Blacklist-POI => x-y-z – mosyflasher Jul 11 '15 at 08:16
  • @mosyflasher: Edit your question and add the details. Then only I can understand and then I will be able to help you. – Raging Bull Jul 11 '15 at 08:53
  • thanks dude , i could figure it out ;) you helped so much – mosyflasher Jul 11 '15 at 20:53