Concatenate multiple rows

Question

I'm using Microsoft SQL Server 2005.

I think I need a sub-query.

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

I would like the output to be:

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

harpo · Accepted Answer · 2010-07-07 18:41:46Z

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

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).

Martin Smith · Accepted Answer · 2010-07-07 18:30:10Z

Here's a way that I'm sure will be bettered by someone that knows SQL Server 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

Collectives™ on Stack Overflow

Concatenate multiple rows

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related