Feb
2
Written by:
Steve Gray
2/2/2010 2:22 PM
This script will give you a comma separated (or semicolon separated) list in SQL Server 2005
SELECT c.sopnumbe,
STUFF(
(SELECT ',' + CAST(itemnmbr AS VARCHAR(MAX)) AS [text()]
FROM (SELECT DISTINCT rtrim(itemnmbr) as itemnmbr
FROM sop10200 AS O
WHERE O.sopnumbe = C.sopnumbe) AS D
ORDER BY itemnmbr
FOR XML PATH('')), 1, 1, '') AS ArrEmps
FROM sop10100 AS C;
Ok, I admit it, that's kind of complicated. Here's a different way:
declare @locncode as varchar(max)
select @locncode = ''
select @locncode = @locncode + ',' + locncode
From _UnitUser
where UserID = @UserID
select stuff(@locncode,1,1,'') as locncode
As always, I welcome your comments!