January 10, 2007 at 11:24 am
The following code:
SELECT
COALESCE(P.PatientId, '') AS PatientId,
P.FirstName + ' ' + COALESCE(P.MiddleName + ' ', '') + P.LastName AS PatientName,
COALESCE(CONVERT(varchar,P.Birthdate, 101), '') AS Birthdate,
COALESCE(P.MedRecNo, '') AS MedRecNo,
COALESCE(Acct.Location, '') AS Location
FROM dbo.PM_PatientData AS P
JOIN dbo.PM_PatientAccount AS Acct ON P.PatientId = Acct.PatientId
WHERE P.PatientId = 2
yields this result-
PatientId PatientName Birthdate MedRecNo Location
----------- ----------------------------------------------------
2 Child Child 09/17/2004 2456524 ER-33-A
2 Child Child 09/17/2004 2456524 IN-163-B
Anybody know how to tweak this so that I only get one row which has the 2 Location values joined in a comma-limited Location field, like this?
PatientId PatientName Birthdate MedRecNo Location
----------- ----------------------------------------------------
2 Child Child 09/17/2004 2456524 ER-33-A, IN-163-B
January 10, 2007 at 11:33 am
I can do this in a stored procedure for a single row (multiple rows with a little fudging).
declare @Location varchar(255)
select @Location = nz(@Location + ' ', '') + nz(Acct.Location, '')
FROM dbo.PM_PatientData AS P
JOIN dbo.PM_PatientAccount AS Acct ON P.PatientId = Acct.PatientId
WHERE P.PatientId = 2
SELECT
COALESCE(P.PatientId, '') AS PatientId,
P.FirstName + ' ' + COALESCE(P.MiddleName + ' ', '') + P.LastName AS PatientName,
COALESCE(CONVERT(varchar,P.Birthdate, 101), '') AS Birthdate,
COALESCE(P.MedRecNo, '') AS MedRecNo,
@Location AS Location
FROM dbo.PM_PatientData AS P
JOIN dbo.PM_PatientAccount AS Acct ON P.PatientId = Acct.PatientId
WHERE P.PatientId = 2
For a small number of records, I use a temp table and a loop.
Russel Loski, MCSE Business Intelligence, Data Platform
January 10, 2007 at 12:36 pm
Use a function.
Check out a previous post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=331770#bm331823
January 10, 2007 at 12:43 pm
Two good solutions- thanks guys!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply