Question

I m, 微软服务器, 2005年。

我认为,我需要一个分局。

寻找每个客户的1(1)行,现有行动领域是每个客户的所有行动组合。

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>

Answer 1

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

我不认为你能够用“XML EXPLICIT”来做到这一点,这样,到2000年,你就会从uck(就简单的解决办法而言)。

Answer 2

在这里,我肯定会由了解Sergek XML的人来改进。

 SELECT CustID  ,
       CustName,
       REPLACE(REPLACE(
       ( SELECT  <option value="  + CAST(ACTION.ActionID AS VARCHAR) +  ">  + ActionName +  </option> 
       FROM    ACTION
               JOIN CustAction
               ON      CustAction.CustID = Cust.CustID
       WHERE   CustAction.ActionID       = ACTION.ActionID FOR XML PATH(  )
       )
       , &lt; , < ), &gt; , > ) AvailableAction
FROM   Cust

友情链接