October 20, 2011 at 8:39 am
The database I have stores notes on a 1 record per row basis. So if I see 3 lines of notes in the application, that data is stored in 3 separate records in the table. I need to write a query for my work orders that contain these notes. But I need to have the notes consolidated into 1 so that my work orders don't get duplicated/triplicated/etc... in the output.
How do I do that?
October 20, 2011 at 9:13 am
hi
try the following script
regards,
Kabelo
CREATE TABLE #Fruits(FruitID INT,FruitName VARCHAR(10))
INSERT INTO #Fruits
VALUES('1001','Apple')
INSERT INTO #Fruits
VALUES('1002','Orange')
INSERT INTO #Fruits
VALUES('1003','Mango')
INSERT INTO #Fruits
VALUES('1004','Banana')
INSERT INTO #Fruits
VALUES('1005','Grape')
--SELECT *FROM #Fruits
DECLARE @FruitNames VARCHAR(8000)
SELECT @FruitNames = COALESCE(@FruitNames + ', ', '') + FruitName
FROM #Fruits
SELECT @FruitNames
DROP TABLE #Fruits
October 20, 2011 at 9:16 am
Thanks. Let me chew on that and see how I can incorporate it. I understand the concept, though. Thanks again.
October 20, 2011 at 9:22 am
The typical way to do this is with FOR XML PATH. Most examples will also use a stuff to remove an initial extraneous delimiter, but I didn't include that because I'm using space as a delimiter and I moved it to the end of the expression, because trailing spaces are not significant in most circumstances.
I assumed that you have a WorkOrders table and a separate WorkOrderNotes table. If everything is in one table instead of two, then your database is not properly normalized.
SELECT *
FROM WorkOrders AS wo
CROSS APPLY (
SELECT WorkOrderNote + ' '
FROM WorkOrderNotes AS won
WHERE wo.WorkOrderID = won.WorkOrderID
ORDER BY Sequence
FOR XML PATH('')
) AS won( WorkOrderNote )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 20, 2011 at 9:24 am
Drew,
It's in 2 tables. And it's not my design, I just have to live with it. Thanks - I'll check this one out.
October 20, 2011 at 9:35 am
Something like this should help:
create table #notes (
NotesId int
, NoteText varchar(100)
)
insert #notes
values (1,'This')
,(1,'is')
,(1,'a')
,(1,'note')
,(2,'This')
,(2,'is')
,(2,'another')
,(2,'note')
select distinct NotesId
, (select NoteText + ' '
from #notes
where NotesId = n.NotesId
for xml path ('')
)
from #notes n
Regards, Iain
Edit: too slow, should check before posting... :blush: 😛
October 20, 2011 at 8:40 pm
Kabelo. (10/20/2011)
hitry the following script
regards,
Kabelo
CREATE TABLE #Fruits(FruitID INT,FruitName VARCHAR(10))
INSERT INTO #Fruits
VALUES('1001','Apple')
INSERT INTO #Fruits
VALUES('1002','Orange')
INSERT INTO #Fruits
VALUES('1003','Mango')
INSERT INTO #Fruits
VALUES('1004','Banana')
INSERT INTO #Fruits
VALUES('1005','Grape')
--SELECT *FROM #Fruits
DECLARE @FruitNames VARCHAR(8000)
SELECT @FruitNames = COALESCE(@FruitNames + ', ', '') + FruitName
FROM #Fruits
SELECT @FruitNames
DROP TABLE #Fruits
Check out Drew's code... it avoids RBAR and handles more than just one return.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply