连接多行 [英] Concatenate multiple rows
本文介绍了连接多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用的是 Microsoft SQL Server 2005.
I'm using Microsoft SQL Server 2005.
我想我需要一个子查询.
I think I need a sub-query.
我正在为每个客户寻找一 (1) 行,AvailableAction 字段是每个客户的所有操作的串联.
I'm looking for one (1) row per customer, with the AvailableAction field be a concatenation of all the Actions for each customer.
use tempdb
GO
IF DB_ID('myDatabase') IS NOT NULL
DROP DATABASE myDatabase
go
CREATE DATABASE myDatabase
GO
USE myDatabase
GO
create table Cust(
CustID Int Identity Primary Key,
CustName Varchar(255)
)
GO
INSERT INTO Cust(CustName) values('One')
INSERT INTO Cust(CustName) values('Two')
GO
CREATE TABLE Action(
ActionID Int Identity(101,1) Primary Key,
ActionName Varchar(128)
)
GO
INSERT INTO Action(ActionName) VALUES('Insert')
INSERT INTO Action(ActionName) VALUES('Update')
INSERT INTO Action(ActionName) VALUES('Delete')
INSERT INTO Action(ActionName) VALUES('Print')
GO
create table CustAction(
CustActionID Int Identity Primary Key,
CustID Int,
ActionID Int
)
GO
INSERT INTO CustAction(CustID,ActionID) VALUES(1,101)
INSERT INTO CustAction(CustID,ActionID) VALUES(1,102)
INSERT INTO CustAction(CustID,ActionID) VALUES(2,102)
INSERT INTO CustAction(CustID,ActionID) VALUES(2,103)
GO
SELECT Cust.CustID,CustName,ActionName
FROM CustAction
JOIN Cust
ON CustAction.CustID = Cust.CustID
JOIN Action
ON CustAction.ActionID = Action.ActionID
GO
SELECT
Cust.CustID,CustName,
'<option value="' + CAST(Action.ActionID AS Varchar) + '">' + ActionName + '</option>' AS AvailableAction
FROM CustAction
JOIN Cust
ON CustAction.CustID = Cust.CustID
JOIN Action
ON CustAction.ActionID = Action.ActionID
我希望输出为:
CustID AvailableAction
1 <option value="101">Insert</option><option value="102">Update</option>
2 <option value="102">Update</option><option value="103">Delete</option>
推荐答案
For 2005+
SELECT CustID
, (SELECT ca.ActionID [@value]
, ActionName [text()]
FROM dbo.CustAction ca
INNER JOIN dbo.Action ON ca.ActionID = Action.ActionID
WHERE ca.CustID = c.CustID
FOR XML PATH('option'), TYPE) AvailableAction
FROM dbo.Cust c
我认为您无法使用 FOR XML EXPLICIT 获得此值,因此如果这是 2000,那么您就不走运了(就简单的解决方案而言).
I don't think you'll be able to get this with FOR XML EXPLICIT, so if this is 2000 you're out of luck (as far as simple solutions go).
这篇关于连接多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文