How to count same Id in a column in a table and based on count perform concatenation
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?开发者_开发百科
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
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
精彩评论