How to concatenate two columns in my case using SQL? [closed]

I have 2 tables called Standards and StandardDetails

Standards

ItemID       ItemCode       BranchID
------------------------------------
135576       555            1111
135576       555            2222

StandardDetails

ItemID       ItemCode       BranchID      RcItemCode      Numbers
-----------------------------------------------------------------
135576       555               1111       555              22
135576       555               1111       556              23

135576       555               2222       555              77
135576       555               2222       556              29
135576       555               2222       557              46

I want to concatenate two columns RcItemCode and Numbers from StandardDetails and expected result looks like this:

ItemID       ItemCode       BranchID    RcCodes
------------------------------------------------------------------
135576       555            1111        555-22,556-23
135576       555            2222        555-77,556-29,557-46

Seriously have no clue for converting rows into column and concatenate it.

Note

I need to make it as a VIEW

Answer

You may try this. Use group by for grouping your id and stuff to concatenate your Code into comma separated values.

; with cte as (
select ItemID,ItemCode,BranchID,RcItemCode + '-' + Numbers as Code from StandardDetails
)
select ItemID,ItemCode,BranchID, 
Stuff( (select ', ' + code from cte as c
       where c.ItemID = ct.ItemID and c.ItemCode = ct.ItemCode  and c.BranchID = ct.BranchID 
       for xml path('')),1,1,'') as RCode 
from cte as ct 
group by ItemID,ItemCode,BranchID

For View

Please try this.

Create view MyView
As
Begin
select ItemID,ItemCode,BranchID, 
Stuff( (select ', ' + code from (select ItemID,ItemCode,BranchID,RcItemCode + '-' + Numbers as Code from StandardDetails) as c
       where c.ItemID = ct.ItemID and c.ItemCode = ct.ItemCode  and c.BranchID = ct.BranchID 
       for xml path('')),1,1,'') as RCode 
from StandardDetails as ct 
group by ItemID,ItemCode,BranchID
End