Concatenate rows of text in SSRS

[jpeters01]

We have a text field that is stored in multiple rows like this:

1/2 tablet every 8 hours Linenumber 1
as needed for headache Linenumber 2
not to exceed 8 tabs Linenumber 3
in 24 hour period Linenumber 4

We need this to display horizontally on our report like this:

1/2 tablet every 8 hours as needed for headache not to exceed 8 tabs in 24 hour period. Need help with the formula please.

 

[jpeters01]

Here is more information regarding this request:

This is an Oracle 9i database.
The patient data is in one table and the comments are in another with a one to many relationship (joined by a textid in each table).
We can't change the database, beings it is a third party vendor application.


Here is the sql:
SELECT
'Inpatient' AS Source,
pt.Active,
pt.Brandname,
pt.Computed_Form,
pt.Computed_FrequencySig,
pt.Computed_ItemOrder,
pt.Description,
NVL(pt.Dosestrength,'0') AS Dosestrength,
pt.FrequencyCode,
pt.HospitalNumber as Patient_ID,
pt.ItemType,
pt.PRN,
pt.Profile,
pt.Room,
pt.Route,
pt.RXNumber,
pt.StartDate,
pt.StopDate,
0 AS Dose_Amount,
'' AS Dose_Due,
'' AS Dose_Unit_Desc,
'' AS Frequency_Desc,
'' AS Last_Taken,
'' AS Med_Hist_Comment,
'' AS Med_Name,
'' AS Route_Description,
'' AS Status_Desc,
pt.Description || pt.Dosestrength ||pt.Computed_FrequencySig as DrugDoseFreq,
tl.linenumber,
tl.textline
FROM
HCS.PATIENTTHERAPY_VIEW pt
INNER JOIN HCS.TEXTLINE tl ON tl.textid = pt.therapycommentstextid
WHERE
pt.HospitalNumber = :AccountNbr AND
(pt.ROUTE IS NULL OR pt.ROUTE NOT IN ('IV','EPID', 'FNB')) AND
pt.PROFILE IN ('IT','IV','MED','TPN') AND
pt.ACTIVE = 'Y' AND
pt.FrequencyCode NOT IN ('NOW','ONCALL','ONCE','STAT','X1') AND
(pt.STOPDATE IS NULL OR pt.STOPDATE >= SYSDATE) AND
(pt.Computed_FrequencySig Is Null Or pt.Computed_FrequencySig=' ' Or (pt.Computed_FrequencySig Not Like '%AS%NEEDED%' AND pt.Computed_FrequencySig Not Like '%PRN%'))

____________________________________________________________
Here is better display of the sample data:

Description Dose PatientID LineNumber Comment
Albuterol-Ipratropium 0 123456789 1 QID/PRN
Calcium Acetate 667 123456789 0
Carvedilol 12.5 123456789 0
Cinacalet 30 123456789 0
Insuline Regular Human 1 unit/0.01 ML 123456789 1 ***Sliding scale regular insulin**
Insuline Regular Human 1 unit/0.01 ML 123456789 3 Give 30 minutes before meal
Insuline Regular Human 1 unit/0.01 ML 123456789 2 (BS-100)/30=# of units of insulin

 

[dhulbert]

I take it the number of comment lines is variable, is there a maximum number it can be?

If there is a sensible maximum number you could look at crudly joining the comments table multiple times
where linenumber = 1
where linenumber = 2
etc

Then concatonate the comment fields into a long string.

If not the more elegant solutuions might be to Pivot the Comments table then link that to your query.

Have a look at Pivot in BOL, or there are loads of posts about it in here.


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[jpeters01]

Thank you...we are working on setting up the pivot now.

 

[RiverGuy]

If you're writing this query against Oracle, I'm not sure how the PIVOT operator in SQL Server will help you. You might ask this question in the Oracle forum.