SQL SERVER 2012 T-SQL

Question

Hi,

Please provide me the query to get output from the below table.

Table:

1 abc Project1
1 def Project1
2 pqr Project2
2 xyz Project2
1 ghi Project1
3 mno Project3

Out put:

1 Project1 abc,def,ghi
2 Project2 pqr,xyz
3 Project3 mno

Thanks,
Rajesh,

Answer 1

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT, Abbr CHAR(3), ProjectName VARCHAR(100));
INSERT INTO @tbl (ID, Abbr, ProjectName) VALUES
(1,'abc','Project1'),
(1,'def','Project1'),
(2,'pqr','Project2'),
(2,'xyz','Project2'),
(1,'ghi','Project1'),
(3,'mno','Project3');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT ID, ProjectName
      , STUFF((SELECT @separator + CAST(Abbr AS VARCHAR(30)) AS [text()]
         FROM @tbl AS O
         WHERE O.id = C.id 
         FOR XML PATH('')), 1, 1, NULL) AS AbbrList
FROM @tbl AS c
GROUP BY ID, ProjectName;

Output

+----+-------------+-------------+
| ID | ProjectName |  AbbrList   |
+----+-------------+-------------+
|  1 | Project1    | abc,def,ghi |
|  2 | Project2    | pqr,xyz     |
|  3 | Project3    | mno         |
+----+-------------+-------------+

Answer 2

Hi @Rajesh Kumar ，

There are different solutions according to different sql server versions.The most common is user-defined functions，any version can be used：

create TABLE test (ID INT, Abbr CHAR(3), ProjectName VARCHAR(100));  
 INSERT INTO test (ID, Abbr, ProjectName) VALUES  
 (1,'abc','Project1'),  
 (1,'def','Project1'),  
 (2,'pqr','Project2'),  
 (2,'xyz','Project2'),  
 (1,'ghi','Project1'),  
 (3,'mno','Project3');  
  
create Function fn_hb(@id Varchar(100))  
Returns Varchar(8000)  
As   
Begin  
Declare @Rst Varchar(8000)  
Set @Rst=''  
Select @Rst=@Rst+Abbr+',' From test Where id=@id  
Set @Rst=Left(@Rst,len(@RSt)-1)  
Return @Rst  
End  
go  
  
select ID,ProjectName,dbo.fn_hb(ID) As Abbr from test group by ID,ProjectName  
  
drop table test   
drop function fn_hb  

sql server 2005 and later versions can use stuff 、for xml (STUFF (Transact-SQL)
、FOR XML (SQL Server)),as provided by YitzhakKhabinsky-0887.

sql server 2017 introduced the string_agg function, which can implement grouping and merging more simply：

create TABLE test (ID INT, Abbr CHAR(3), ProjectName VARCHAR(100));  
 INSERT INTO test (ID, Abbr, ProjectName) VALUES  
 (1,'abc','Project1'),  
 (1,'def','Project1'),  
 (2,'pqr','Project2'),  
 (2,'xyz','Project2'),  
 (1,'ghi','Project1'),  
 (3,'mno','Project3');  
  
select ID,ProjectName,string_agg(Abbr,',') Abbr from test   
group by ID,ProjectName  
  
drop table test   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.