ParentID    Name                                               
----------- -------------------------------------------------- 
1           Agencey 1
2           Agencey 2
3           Both

(3 row(s) affected)

CREATE TABLE ACodes ([id] int IDENTITY(1,1), [Name] varchar(50))
CREATE TABLE Capacity ([Parentid] int, ACodeID int)
GO

INSERT INTO ACodes ([Name])
SELECT 'Agencey 1' UNION ALL SELECT 'Agencey 2'
GO

INSERT INTO Capacity (ParentID, ACodeID)
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 2
GO

SELECT DISTINCT ParentID, 'Both' AS [Name] FROM Capacity
WHERE ParentID in (	SELECT ParentID
			  FROM Capacity c
		      GROUP BY ParentID
		        HAVING COUNT(*) > 1)
UNION ALL
SELECT ParentID, [Name]
FROM Capacity c INNER JOIN ACodes a ON c.ACodeID = a.[ID]
WHERE ParentID in (	SELECT ParentID
			  FROM Capacity c
		      GROUP BY ParentID
		        HAVING COUNT(*) = 1)
ORDER BY ParentID
GO

DROP TABLE ACodes, Capacity
GO

SELECT		c.ParentID,
		CASE
			WHEN MIN(c.aCodeID) < MAX(c.aCodeID) THEN 'Both'
			ELSE MIN(ac.Name)
		END AS Name
FROM		aCodes AS ac
INNER JOIN	Capacity AS c ON c.aCodeID = ac.ID
GROUP BY	c.ParentID

--Peso
SELECT		d.ParentID,
		COALESCE(a.Name, 'Both') AS Name
FROM		(
			SELECT		ParentID,
					CASE
						WHEN MIN(aCodeID) = MAX(aCodeID) THEN MAX(aCodeID)
						ELSE NULL
					END AS CodeID
			FROM		Capacity
			GROUP BY	ParentID
		) AS d
LEFT JOIN	aCodes AS a ON a.ID = d.CodeID

quote:

---

Originally posted by visakh16

Try this:-
SELECT c.ParentID,
CASE WHEN COUNT(a.ID) > 1 THEN 'Both' ELSE MAX(a.[Name]) END AS Name
FROM Capacity c
INNER JOIN ACodes a
ON a.ID=c.ACodeID
GROUP BY c.ParentID

---