none
Combining multiple rows together from two tables

    Question

  • I have two tables, one contains user information, the other contains comments.  I want to select each row in the users column, and all of the comments for that specific user.  However, I want to combine the comments into a single row, I don't want multiple rows for each comment.

    --Create/Populate [#Names]:
     if object_id('tempdb..[#Names]','U') is not null drop table [#Names]
     go
     create table [#Names]
     (
      [nameid] int
      ,[namelast] varchar(50)
      ,[namefirst] varchar(50)
     )
     go
     set nocount on
    insert [#Names] select 1, 'Smith', 'John'
    insert [#Names] select 2, 'Doe', 'Jane'
    insert [#Names] select 3, 'Doe', 'John'
     go
    --Create/Populate [#Comments]:
     if object_id('tempdb..[#Comments]','U') is not null drop table [#Comments]
     go
     create table [#Comments]
     (
      [commentid] int
      ,[nameid] int
      ,[commenttext] varchar(MAX)
     )
     go
     set nocount on
    insert [#Comments] select 1, 1, 'New user.'
    insert [#Comments] select 2, 1, 'Promoted'
    insert [#Comments] select 3, 2, 'Suspended on June 12'
    insert [#Comments] select 4, 2, 'Terminated June 15'
    insert [#Comments] select 5, 3, 'New hire'
     go
    select namefirst, namelast, commenttext
    from [#Names] a
    JOIN [#Comments] b
    ON a.nameid = b.nameid
    

    Running this will show 5 rows.  I want to combine rows 1 and 2, 3 and 4, so there are only 3 rows returned, with all the comments for a specific user in a single row.
    Monday, July 22, 2013 4:33 PM

Answers

All replies