output issue

[Chumley40]

I am writing a function. This is what I want:
I run a query that returns users with fields and dates that they entered them.

user1 date field1
user1 date field2
user1 date field3
user2 date field4
user2 date field5

I want it to return

"user1 date field1,field2, field3" (0ne field)
user2 date field4, field5 (one field)

This is what I have currently:
DECLARE @output varchar(4000)
DECLARE @Sep CHAR(3)

SET @Sep = ','
declare @UserSig Table
(
Displayname varchar (100),
Obs varchar (2000))

Insert @UserSig
Select distinct
cv3user.displayname + ' (' + cv3user.occupationcode + ')',
obs = LEFT (LTRIM(RTRIM(ISNULL(ocmi.LeftJustifiedLabel, '')
+ ' ' + ISNULL(ocmi.RightJustifiedLabel, ''))),200)
from cv3user
Inner JOIN CV3Observation COBS WITH (NOLOCK)
ON cobs.userguid = cv3user.guid
Inner JOIN CV3ObservationDOcument COD WITH (NOLOCK)
ON COD.OwnerGUID = 1602040
and COBS.GUID = COD.ObservationGUID
and cod.active = 1

Inner Join cv3obscatalogmasteritem ocmi WITH (NOLOCK)
on ocmi.guid = cod.obsmasteritemguid

where cobs.recordedproviderguid <> cv3user.guid

SELECT
@output =
CASE
WHEN @output IS NULL THEN
Displayname + ' ' + Obs

ELSE @output+ @Sep + ' ' + displayname+ ' ' + obs
END

FROM @UserSig

select @output

This is giving me user1, field1, user1 field2, user1 field3 etc.

 

[JackVam]

Can you not create another variable e.g @output2 and distribute across the 2 variables?

All the IT jobs in one place -

http://www.ITjobfeed.com