Group by contact - SQL Server

Question

I am trying to merge or concat columns based on two column values.

ID          ID_1        Name    Value   ID_3
NULL          1          A        x      NULL
NULL          1          B        x      NULL
NULL          1          A        y      NULL
NULL          2          C        p      NULL
3           NULL         A        x      NULL
3           NULL         B        x      NULL
3           NULL         A        y      NULL
4           NULL         C        p      NULL

Expected Result :

ID          ID_1        Name    Value   ID_3
NULL          1          AB       x      NULL
NULL          1          A        y      NULL
NULL          2          C        p      NULL
3            NULL        AB       x      NULL
3            NULL        A        y      NULL
4            NULL        C        p      NULL

For Id_1 , I want to concat Name but only where value column has same value. Similarly for ID_2 as well.

**see this Answer** [https://stackoverflow.com/questions/51331640/how-to-write-the-t-sql-string-agg-function/51339959#51339959](https://stackoverflow.com/questions/51331640/how-to-write-the-t-sql-string-agg-function/51339959#51339959) — Rami Bancosly, Jul 30 '18 at 08:59

D-Shih · Answer 1 · 2018-07-30T08:21:44.790

If it is SQL Server 2017 you can try to use string_agg function.

select ID,ID_1,string_agg(Name, '') 'Name',Value,ID_3
 from T 
group by ID,ID_1,Value,ID_3

sqlfiddle

[Results]:

|     ID |   ID_1 | Name | Value |   ID_3 |
|--------|--------|------|-------|--------|
| (null) |      1 |   AB |     x | (null) |
| (null) |      1 |    A |     y | (null) |
| (null) |      2 |    C |     p | (null) |
|      3 | (null) |   AB |     x | (null) |
|      3 | (null) |    A |     y | (null) |
|      4 | (null) |    C |     p | (null) |

If your SQL Server version lower than 2017, you can try to use with FOR XML and PATH.

SELECT 
  ID,
  ID_1,
  STUFF((
    SELECT Name
    FROM T
    WHERE 
        Value = t1.Value 
    and 
        (ID = t1.ID or ID_1 = t1.ID_1)
     FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
  ,1,0,'') AS 'Name',
    Value,
    ID_3
FROM T t1
group by ID,ID_1,Value,ID_3

sqlfiddle

score 0 · Answer 2 · answered Jul 30 '18 at 10:46

If you have at most two rows that need to be aggregated, then a simpler method is:

select id, id_1,
       (case when min(name) = max(name) then min(name)
             else min(name) + max(name)
        end) as names
       value, id_3
from t
group by id, id_1, value, id_3;

If you don't know that there are only up to two matches, then you need to do a more sophisticated string aggregation.

Group by contact - SQL Server

2 Answers2