Monday, November 22, 2010

SQL SERVER: Generate Comma Separated List with SELECT statement

Hi everyone,
Many times i require the data from select command in comma seperated format
for e.g.

SELECT  [AdminId]
      ,[LoginId]
      ,[Password]
      ,[AdminType]
      ,[TaskId]
      ,[CreatedBy]
      ,[Status]
  FROM [CMS2012].[dbo].[Admin_Details]
  where AdminId in(3,4,5,6)
will give me result as...



But what i need is...
Login id:-
Mexus,Vibhorthegame@yahoo.co.in,qwe,123@mexuseducation.com
Password:-
Mexus,123123,qwe,123123
etc.....
so i wrote this SQL select statement...


Declare @Description varchar(4000)
Declare @password varchar(max)

select @Description  = coalesce(@Description + ',','')  + LoginId ,
@password =coalesce(@password+',','')+Password
FROM Admin_Details  where AdminId  in (3,4,5,6)

SELECT
     @Description ,@password

And what i got is...



Happy Coding,
Regards,
Krunal Panchal

No comments:

Post a Comment