Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: How do I aggregate a column by concatenation?

    How do I aggregate a column by concatenation?


    /*
    SQL Server 2000 datbase

    I have 3 tables.
    A "client" table.
    A "call history" table.
    And a "call detail" table.

    The "client" table contains data about individual clients.
    The "call history" table contains data about calls to or from the clients in the "client" table. (This is a one-to-many relationship of client-to-history interactions.)
    The "call detail" table contains detail data about a particular "call history" entry. (This is also a one-to-many relationship of history-to-detail entries.)
    */

    Code:
    DECLARE @Client TABLE (ClientID INT PRIMARY KEY, ClientName VARCHAR(100))
    INSERT INTO @Client
    		  SELECT 1 [ClientID], 'Client A' [ClientName]
    UNION ALL SELECT 2, 'Client B'
    UNION ALL SELECT 3, 'Client C'
    UNION ALL SELECT 4, 'Client D'
    
    SELECT c.ClientID, c.ClientName
    FROM @Client c
    ORDER BY c.ClientID
    
    DECLARE @CallHistory TABLE (ClientID INT, CallID INT PRIMARY KEY, [TimeStamp] DATETIME, InteractionDesc VARCHAR(100))
    INSERT INTO @CallHistory
    		  SELECT 1 [ClientID], 1 [CallID], GETDATE()-10 [TimeStamp], 'Request Information' [InteractionDisc]
    UNION ALL SELECT 1, 2, GETDATE()-9, 'Purchase'
    UNION ALL SELECT 1, 3, GETDATE()-8, 'Request Information'
    UNION ALL SELECT 2, 4, GETDATE()-7, 'Request Information'
    UNION ALL SELECT 2, 5, GETDATE()-6, 'Request Information'
    UNION ALL SELECT 3, 6, GETDATE()-5, 'Purchase'
    UNION ALL SELECT 4, 7, GETDATE()-4, 'Request Information'
    UNION ALL SELECT 4, 8, GETDATE()-3, 'Request Information'
    
    SELECT c.ClientID, c.ClientName, ch.CallID, ch.[TimeStamp], ch.InteractionDesc
    FROM @Client c
    JOIN @CallHistory ch
    ON ch.ClientID = c.ClientID
    ORDER BY c.ClientID, ch.[TimeStamp] DESC
    
    DECLARE @CallDetail TABLE (ClientID INT, CallID INT, CallDetailID INT PRIMARY KEY, Detail VARCHAR(100))
    INSERT INTO @CallDetail
    		  SELECT 1 [ClientID], 1 [CallID], 1 [CallDetailID], 'Item A' [Detail]
    UNION ALL SELECT 1, 1, 2, 'Item B'
    UNION ALL SELECT 1, 2, 3, 'Item A'
    UNION ALL SELECT 2, 4, 4, 'Item B'
    UNION ALL SELECT 2, 5, 5, 'Item A'
    UNION ALL SELECT 2, 5, 6, 'Item B'
    UNION ALL SELECT 4, 7, 7, 'Item A'
    UNION ALL SELECT 4, 7, 8, 'Item B'
    
    SELECT c.ClientID, c.ClientName, ch.CallID, ch.[TimeStamp], ch.InteractionDesc, cd.Detail
    FROM @Client c
    JOIN @CallHistory ch
    ON ch.ClientID = c.ClientID
    LEFT JOIN @CallDetail cd
    ON cd.ClientID = c.ClientID
    AND cd.CallID = ch.CallID
    ORDER BY c.ClientID, ch.[TimeStamp] DESC
    /*
    Is there a way to aggregate the CallDetail Detail column so that if multiple Left Joined entries are present (per each CallHistory at that TimeStamp), so that a rows would look like:
    1 Client A 3 2012-03-20 10:59:06.030 Request Information NULL
    1 Client A 2 2012-03-19 10:59:06.030 Purchase Item A
    1 Client A 1 2012-03-18 10:59:06.030 Request Information Item A;Item B
    2 Client B 5 2012-03-22 10:59:06.030 Request Information Item A;Item B
    2 Client B 4 2012-03-21 10:59:06.030 Request Information Item B
    3 Client C 6 2012-03-23 10:59:06.030 Purchase NULL
    4 Client D 8 2012-03-25 10:59:06.030 Request Information NULL
    4 Client D 7 2012-03-24 10:59:06.030 Request Information Item A;Item B
    */

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why doesn't this do what you want?

    SELECT c.ClientID, c.ClientName, ch.CallID, ch.[TimeStamp]
    , ch.InteractionDesc, cd.Detail
    FROM @Client c
    JOIN @CallHistory ch
    ON ch.ClientID = c.ClientID
    LEFT JOIN @CallDetail cd
    ON cd.ClientID = c.ClientID
    AND cd.CallID = ch.CallID
    ORDER BY c.ClientID, ch.[TimeStamp] DESC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by Brett Kaiser View Post
    Why doesn't this do what you want?

    SELECT c.ClientID, c.ClientName, ch.CallID, ch.[TimeStamp]
    , ch.InteractionDesc, cd.Detail
    FROM @Client c
    JOIN @CallHistory ch
    ON ch.ClientID = c.ClientID
    LEFT JOIN @CallDetail cd
    ON cd.ClientID = c.ClientID
    AND cd.CallID = ch.CallID
    ORDER BY c.ClientID, ch.[TimeStamp] DESC
    This gives multiple Call History entries for each Call Detail that is present.
    I want to group concatenate each CallDetail.Detail (aggregate) so that I have only one Call History entry for each CallID.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So you only want 1 row for Call ID 7, But you have 2 details.

    WHICH Details do you want to see?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by Brett Kaiser View Post
    So you only want 1 row for Call ID 7, But you have 2 details.

    WHICH Details do you want to see?
    I would like to see all the Details available, as a concatenated string. (Like: "Item A;Item B")

    In SQL 2005, I know that I can write this as a sub query using FOR XML PATH:

    SELECT c.ClientID, c.ClientName, ch.CallID, ch.[TimeStamp], ch.InteractionDesc
    ,(SELECT Detail + ';' FROM @CallDetail WHERE ClientID = c.ClientID AND CallID = ch.CallID FOR XML PATH('')) [Detail]
    FROM @Client c
    JOIN @CallHistory ch
    ON ch.ClientID = c.ClientID
    ORDER BY c.ClientID, ch.[TimeStamp] DESC

    But this does not work in SQL 2000.

    What is the equivalent in SQL 2000?
    Last edited by DivingDragon; 03-28-12 at 15:11.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First order of business, this is a presentation issue so it ought to be handled as close to the user as possible. Presentation details like concatenation of column values from grouped rows definitely should not be handled by the database.

    This can be done in SQL 2000, but it is really ugly. You should fix the problem instead of working around it if possible.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •