Post reply

Merging rows in a table

  • February 28, 2007 at 9:59 am

    #369701

    I have a table that looks like this

    Date (datetime field)

    INCIDENT (int)

    Comments (varchar(100))

    Each record is spanned over multiple rows. For example INCIDENT 1010 on 12/20/2006 has 10 records because comments field wasn't big enough. I need to export this data into a new table and for incident 1010 I need to merge all 10 comments into one field so each record is only one row. I'm not sure how to do this. Can anyone help? The date and INCIDENT field concatenated together will form the unique key for each record.

     

    Thanks!

     

    Thanks!

  • February 28, 2007 at 10:13 am

    #692354

    This may help:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=347037#bm347099

     

  • February 28, 2007 at 11:00 am

    #692370

    Thanks, but not working - I still can't get all the comments into one record. I'm not that good at tsql and I can't seem to get it right. Do you have any more examples?

    Thanks!

  • February 28, 2007 at 11:17 am

    #692371

    This assumes comments will never exceed 8000 characters.

    -- *** Test Data ***

    CREATE TABLE dbo.YourTable

    (

        [Date] datetime NOT NULL

        ,Incident int NOT NULL

        ,Comments varchar(100) NOT NULL

    )

    GO

    INSERT INTO dbo.YourTable

    SELECT '20070228', 1, 'c1' UNION ALL

    SELECT '20070228', 1, 'c2' UNION ALL

    SELECT '20070228', 1, 'c3' UNION ALL

    SELECT '20070228', 2, 'c1' UNION ALL

    SELECT '20070228', 2, 'c2'

    -- *** End Test Data ***

    -- Create function

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION dbo.RollupComments

    (

        @Date datetime

        ,@Incident int

    )

    RETURNS varchar(8000)

    AS

    BEGIN

        DECLARE @Result varchar(8000)

        SET @Result = ''

        SELECT @Result = @Result + Comments -- + ' ' -- ???

        FROM dbo.YourTable

        WHERE [Date] = @Date

            AND Incident = @Incident

        RETURN @Result

    END

    GO

    -- Now do query

    SELECT [Date], Incident

            ,dbo.RollupComments([Date], Incident) AS Comments

    FROM (

            SELECT DISTINCT [Date], Incident

            FROM dbo.YourTable

        ) D

     

  • February 28, 2007 at 3:58 pm

    #692461

    Thanks Ken...this is working. I ran the query after creating the function and it ran for over 2 hours until I stopped it, but it is working for the records it completed. There are almost a million records in this table and I'm running it on our test virtual machine so it's quite slow. Thanks for your help.

    Thanks!

  • March 1, 2007 at 3:10 am

    #692530

    An index on [Date], Incident should help with the speed.

     

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

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