Friday, August 1, 2014

GROUP BY clause to get comma separated values in MS SQL

Today we will see how to get the comma separated values from SQL table. This may require during the project development to send the report or to send an email to multiple people.
 
We will create the table first.
CREATE TABLE [dbo].[StatusReport](
    [StudentID] [int] NULL,
    [Status] [nchar](10) NULL,
    [Email] [varchar](50) NULL
)

 
Now we will insert some data in this table,
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (1 ,'Fail' ,'Prasanna@yahoo.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (2 ,'Pass' ,'Pass@gmail.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (3 ,'Fail' ,'Jwel@yahoo.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (4 ,'Pass' ,'Priya@hotmail.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (5 ,'Fail' ,'Deep@gmail.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (6 ,'Pass' ,'amm@yahoo.com')
INSERT INTO [dbo].[StatusReport] ([StudentID] ,[Status] ,[Email]) VALUES (7 ,'Fail' ,'Ram@yahoo.com')
 
Now we will write the code to get the result as comma separated.
SELECT [Status], Email = 
STUFF((SELECT ', ' + Email FROM [StatusReport] b 
WHERE b.Status = a.Status 
FOR XML PATH('')), 1, 2, '') 
FROM [StatusReport] a 
GROUP BY Status 
 
lets see the out put of this query,
image
I hope above query is clear and you will be able to write your own as per the requirement.






2 comments:

Anonymous said...

sir i need programming of all video formatts i request to you post articles about video formatts programming.

Cub Training said...

It was so nice article and useful to Informatica learners. we also provide Dotnet Course online training our Cubtraining is leader in providing Software Training