代码之家 › 专栏 › 技术社区 › Liam neesan

在我的例子中,如何使用SQL连接两列?[暂停]

sql-server-2012 sql-server sql

-1

Liam neesan · 技术社区 · 5 年前

我有两张叫的桌子 Standards 和 StandardDetails

标准

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

标准详细信息

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

我想连接两列 RcItemCode 和 Numbers 从 标准详细信息 预期结果如下:

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

严重的是,对于将行转换为列并将其连接起来没有任何线索。

注释

我需要把它作为一个视图

1 回复 | 直到 5 年前

DarkRob 5 年前

你可以试试这个。使用 group by 用于分组 id 和 stuff

; 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

查看

请试试这个。


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