Concatenating column values from table-2 based on the value from table -1

Question

Possible Duplicate:
Combining rows of queried results by unique identifier?

I have two tables in Sql Server 2008, like below

Table1

       DealNum            DealVresion            Value

        1000                   1                 100
        1000                   2                 200
        1000                   3                 150  
        1000                   4                 130
        1001                   2                  70
        1003                   5                 160
        1003                   0                 120

Table 2

     DealNum            DealVersion              Name 

     1000                    1                   John
     1000                    1                   Bob
     1000                    2                   John
     1000                    2                   Merle
     1000                    3                   Bob
     1000                    3                   Bob
     1000                    5                   Bob
     1001                    2                   Smith
     1001                    2                   stone
     1002                    8                   Andrew
     1003                    5                   Smith
     1003                    0                   Martin
     1003                    0                   Narine

Now I want a left join on these two tables based on

     (T1.Dealnum= T2.Dealnum) and (T1.Deal Version = T2.Deal Version)

and I want the Name from Table2 to be concatenated so that it won't show any duplicates in DealNum.

Result Required:

         DeaLNum           Deal Version          Value          Name

          1000                  1                 100           Jhon,Bob
          1000                  2                 200           John,Merle
          1000                  3                 150           Bob
          1000                  4                 130           NULL
          1001                  2                 70            Smith,Stone                  
          1003                  0                 120           Martin,Narine
          1003                  5                 160           Smith

It has to concatenate the names column for the DealNum and version. If the same dealNum and Version has the same name then no need to Concatenate(ex: 1000 - 3)

Thanks In advance Harry

[String.Join in SQL](http://stackoverflow.com/questions/4815260/string-join-in-sql) or [Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su). — Tim Schmelter, Jul 17 '12 at 13:34
"Now I want a left join doing a left join on these two tables based on" .....I am assuming this is just a single left join? — Hituptony, Jul 17 '12 at 13:37
Sorry, I have edited the question .. I want a LeftJoin with no duplicates because of the Name in the result. (That's why I'm asking to concatenate) — harry, Jul 17 '12 at 13:48

score 1 · Accepted Answer · answered Jul 17 '12 at 13:55

You can do this by creating a user defined function, don't know if it can be done in a single select statement:

    create function getNameList(@dealnum int, @dealversion int) returns nvarchar(max)
    begin
    declare @name varchar(max)

    select  @name = coalesce(@name + ', ','') + name
    from    (select distinct name from table2 where dealnum = @dealnum and dealversion = @dealversion) t1

    return @name
    end

then:

    select  distinct
            t1.dealNum,
            t1.dealVersion,
            t1.value,
            dbo.getNameList(t1.dealNum, t1.dealversion)
    from    table1 t1 join table2 t2 on t1.dealnum = t2.dealnum and t1.dealversion = t2.dealversion

I'd compare the performance of this to other solutions you've been offered. While this works, I imagine it's going to be a problem at scale. — Aaron Bertrand, Jul 17 '12 at 14:31

Aaron Bertrand · Answer 2 · 2022-01-24T21:13:39.563

1

;WITH y AS
(
  SELECT DealNum, DealVersion, Name = STUFF((SELECT ',' + y2.Name
    FROM dbo.Table2 AS y2 
    WHERE y2.DealNum = y.DealNum AND y2.DealVersion = y.DealVersion
    GROUP BY y2.Name
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, '') 
  FROM dbo.Table2 AS y
)
SELECT x.DealNum, x.DealVersion, x.Value, y.Name
FROM dbo.Table1 AS x 
LEFT OUTER JOIN y 
ON x.DealNum = y.DealNum
AND x.DealVersion = y.DealVersion
GROUP BY x.DealNum, x.DealVersion, x.Value, y.Name;

edited Jan 24 '22 at 21:13

answered Jul 17 '12 at 13:56

Aaron Bertrand

272,866
37
466
490

Hi Aaron, Thanks for the answer, this one is working, but I don't want to have a group by clause, because I'm doing this query on a big table with many columns. – harry Jul 17 '12 at 14:26
@harry so you don't want to type the columns? You know you can [drag the columns onto the query window from Object Explorer](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx), right? You can use DISTINCT instead if you really want, but that is probably going to perform worse. – Aaron Bertrand Jul 17 '12 at 14:28

score 1 · Answer 3 · answered Jul 17 '12 at 14:11

try this:

  with cte as (
  select distinct T1.DealNum [T1_DealNum],T1.DealVresion [T1_DealVresion],
 T1.Value [T1_Value],
  T2.DealNum [T2_DealNum],T2.DealVresion [T2_DealVresion],T2.Name [T2_Name]
  from Table1 T1 left outer join Table2 T2
  on (T1.Dealnum= T2.Dealnum) and (T1.DealVresion = T2.DealVresion)
  )
  select [T1_DealNum],[T1_DealVresion],[Name]=  

  STUFF((SELECT ', ' + [T2_Name]
       FROM cte b 
       WHERE a.[T1_DealNum]= b.[T2_DealNum]
       and a.[T1_DealVresion] = b.[T2_DealVresion]
      FOR XML PATH('')), 1, 2, '')
FROM cte a
GROUP BY [T1_DealNum],[T1_DealVresion]
order by [T1_DealNum],[T1_DealVresion]

Concatenating column values from table-2 based on the value from table -1

3 Answers3