How do I consolidate multiple rows into 1 row of output

  • 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?

  • 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

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

  • 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

  • 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.

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

  • 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)

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

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