December 2, 2010 at 12:17 pm
Need to concatenated multiple rows into one field during a select statement in SSRS?
The text 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.
Help please...
December 2, 2010 at 1:27 pm
How is this stored in the database? Can you get us some sample data and the table definition as per the first link in my signature?
It seems like this would be something you'd want to do at the database, and not in the report as it would most likely be faster and would be less data to push across the wire as your report is rendering.
-Luke.
December 2, 2010 at 1:56 pm
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%'))
Hope this helps.
Sharon
December 2, 2010 at 2:06 pm
Sorry, here is sample data:
Description Dose PatientIDLineNumber Comment
Albuterol-Ipratropium 0 1234567891 QID/PRN
Calcium Acetate 667 1234567890
Carvedilol 12.5 1234567890
Cinacalet 30 1234567890
Insuline Regular Human1 unit/0.01 ML 1234567891 ***Sliding scale regular insulin**
Insuline Regular Human1 unit/0.01 ML 1234567893 Give 30 minutes before meal
Insuline Regular Human1 unit/0.01 ML 1234567892 (BS-100)/30=# of units of insulin
Thanks.
Sharon
April 14, 2011 at 6:45 am
I, too, have a similar problem., and am new to SQL. I have a DB2 file coming from an AS400 that has PART, Line#, and a text description.
AS400 file is ITMEXTD and has fields, PART (char 25), LINE (numeric (5,0), EXTDSC(char 50). There are multiple lines per part. Some would only have 1 or 2 lines, others maybe 10 lines, and some none.
I need to merge EXTDSC field for each part into a SQL 2005 database ITEMS which has PART(char 25), DESCR (char 60), EXTDESC (CHAR 2500)., UOM (char 2), ... This file has only 1 PART per product with the other data in the file coming from unique by part, PARTMSTR file from AS400.
Does anyone have any ideas how to get the data from ITEMEXTD by part and update the many text EXTDSC fields into 1 field EXTDESC in ITEMS?
April 14, 2011 at 10:35 am
This is a bit of code that I believe originated on Pinal Dave's website. It takes column data and turns it in to a comma delimited list. I think it will do what you need....more or less.
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO
April 14, 2011 at 11:45 am
Thank you, but it is a bit more complicated than that. I have 2 tables.
table 2 table 2
part line description part description extradescription
AL123 1 This part is dangerous...AL123 Nitro Glycerin null
AL123 2 Hazardous material.
What I want to do is update the data from Table 1 to look like this in Table 2
Part Description ExtraDescription
AL123 Nitro Glycerin...............This part is dangerous. Hazardous material.
April 14, 2011 at 12:04 pm
Would something like this work?
DECLARE @DescriptionList varchar(max)
--Create temp table
CREATE #TempTable (
part varchar(6),
line int,
description varchar(50)
)
INSERT INTO @TempTable(part, line, description)
(SELECT part, line, description
FROM Table1
UNION
SELECT part, 1, description
FROM Table2
UNION
SELECT part, 1, extradescription
FROM Table 2)
---------------
Select @DescriptionList = COALESCE(@DescriptionList + ', ', '') + [description]
FROM @TempTable
---------------
April 14, 2011 at 12:23 pm
Thank you, but I do not want the line # in Table 2. I want to concatenate the extra descriptions from the 2 records in Table1 and update the 1 record in Table2
April 15, 2011 at 7:34 am
I have found the answer. In case someone else has a similar problem, here is the SQL.
- Update the Long extra description from FKITEXTD Added by Joan
UPDATE U
SET U.CUMEXT = X.EXTDESCRIPTION
FROM DHUPLXLS U
INNER JOIN
(
SELECT P.IMPN AS PART,
( SELECT L.REEDSC + ' '
FROM FKITEXTD L
WHERE P.IMPN = L.IMPN AND L.IMCO = 100
ORDER BY L.IMPN, L.RELINE
FOR XML PATH('') ) AS ExtDescription
FROM FKITEXTD P
WHERE P.IMCO = 100
GROUP BY P.IMPN
) X ON U.CUMPN = X.PART
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply