Ich verwende Microsoft SQL Server 2005.

Ich glaube, ich brauche eine Unterabfrage.

Ich suche nach einer (1) Zeile pro Kunde, wobei das Feld AvailableAction eine Verkettung aller Aktionen für jeden Kunden ist.

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

Ich möchte, dass die Ausgabe wie folgt lautet:

CustID AvailableAction

    1  <option value="101">Insert</option><option value="102">Update</option>
    2  <option value="102">Update</option><option value="103">Delete</option>