SQL SERVER:Generating CSV in select command and again Generating Row wise data

Hi everyone,


Declare @chapters varchar(max)
select @chapters=coalesce(@chapters+',','')+ch.Chapter
from Chapter ch,Subject sb ,Syllabus s,Classroom c
--for syllabus
where s.SyllabusID ='3'
--for classroom
and c.SyllabusID =s.SyllabusID
and c.Classroom =1
--for subject
and sb.SyllabusID =s.SyllabusID
and sb.ClassroomID =c.ClassroomID
--and sb.Subject ='Eng.Lit.'
--for chapters
and ch.SyllabusID =s.SyllabusID
and ch.ClassroomID =c.ClassroomID
and ch.SubjectID =sb.SubjectID

select @chapters

Select * from UF_CSVToTable (@chapters)

Function UF_CSVToTable 


USE [CMS2012]
GO
/****** Object:  UserDefinedFunction [dbo].[UF_CSVToTable]    Script Date: 11/22/2010 17:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[UF_CSVToTable]
(
 @psCSString VARCHAR(max)
)
RETURNS @otTemp TABLE(sID VARCHAR(2000))
AS
BEGIN
 DECLARE @sTemp VARCHAR(max)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END

OutPut:

Happy Coding,
Regards,
Krunal Panchal

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