How to count same Id in a column in a table and based on count perform concatenation.

Question

I have a table in below format

ID Name 1 Amit 2 Mohit 2 Nahar

My reqiurement is to get a query that will update this table to below format

ID Name 1 Amit 2 Mohit OR NAHAR

Is there any query in SQL that can solve this purpose?

Answer 1

You can try something like this to get the names concatenated.

DECLARE @Table TABLE(
     ID INT,
     NAME VARCHAR(MAX)
)

DECLARE @TempTable TABLE(    
     ID INT,
     NAME VARCHAR(MAX)
)

INSERT INTO @Table (ID,[NAME]) SELECT 1, 'A'
INSERT INTO @Table (ID,[NAME]) SELECT 2, 'B'
INSERT INTO @Table (ID,[NAME]) SELECT 2, 'C'

DECLARE @ID INT
DECLARE Cur CURSOR FOR 
SELECT  DISTINCT 
     ID
FROM    @Table

OPEN Cur
FETCH NEXT FROM Cur INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Name VARCHAR(MAX)
    SET @Name = NULL
    SELECT @Name = COALESCE(@Name + ' OR ' + NAME, Name)
    FROM @Table
    WHERE ID = @ID
    INSERT INTO @TempTable (ID,[NAME]) SELECT @ID, @Name
    FETCH NEXT FROM Cur INTO @ID
END

CLOSE Cur
DEALLOCATE Cur

SELECT * FROM @TempTable

Answer 2

Try this

DECLARE @Table TABLE(       
        ID INT,
        NAME VARCHAR(MAX)
)
    INSERT INTO @Table (ID,[NAME]) SELECT 1, 'Amit'
INSERT INTO @Table (ID,[NAME]) SELECT 2, 'Mohit'
INSERT INTO @Table (ID,[NAME]) SELECT 2, 'Nahar'
INSERT INTO @Table (ID,[NAME]) SELECT 3, 'C'
INSERT INTO @Table (ID,[NAME]) SELECT 3, 'D'
INSERT INTO @Table (ID,[NAME]) SELECT 3, 'E'

Query(For the first query)

select id,
     REPLACE(stuff((select ',' + '  ' + name  + '  ' 
      from @Table b
      where b.id = a.id
      FOR xml path('')),1,1,' '),',','OR') MergedData
from @Table a
group by a.id

Output:

id  MergedData
1      Amit  
2      Mohit  OR  Nahar

Query (For the change request)

select distinct a.id,

    case when coalesce(x.cnt,0) <= 2 then

     REPLACE(stuff((select ',' + '  ' + name  + '  ' 
      from @Table b
      where b.id = a.id
      FOR xml path('')),1,1,' '),',','OR') 


      when x.cnt > 2 then 

      REPLACE(stuff((select ',' + '  ' + name  + '  ' 
      from @Table b
      where b.id = a.id
      FOR xml path('')),1,1,' '),',','AND') 

      end

      MergedData
from @Table a

left join 

(select id,COUNT(id) cnt
from @Table 
group by ID
having (COUNT(id)>1))x
on a.ID = x.ID

Output:

id  MergedData
1      Amit  
2      Mohit  OR  Nahar  
3      C  AND  D  AND  E