How do I consolidate multiple rows into 1 row of output

Question

Post reply

How do I consolidate multiple rows into 1 row of output

gbankos

Ten Centuries

Points: 1122
More actions
October 20, 2011 at 8:39 am

#259025

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kabelo. SSC Enthusiast Points: 175 More actions · Answer 1

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

gbankos Ten Centuries Points: 1122 More actions · Answer 2

Thanks. Let me chew on that and see how I can incorporate it. I understand the concept, though. Thanks again.

drew.allen SSC Guru Points: 76985 More actions · Answer 3

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

gbankos Ten Centuries Points: 1122 More actions · Answer 4

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.

irobertson Hall of Fame Points: 3695 More actions · Answer 5

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: 😛

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Kabelo. (10/20/2011)
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

Check out Drew's code... it avoids RBAR and handles more than just one return.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)