T-SQL Ref Guide

Convert Datetime field to just date field for comparing the date part and ignoring the time part:

           

              Convert(DATE,OrderDatetime,102)='2014-01-01'

 

Getting the Rows to Column in a Comma separated value

Step1:

Create a Table type

CREATE TYPE TABLEVALUES AS TABLE
(

    VALUE1 NVARCHAR(100)

)

Step2:

Create a function to return the values in CSV
      Create FUNCTION UFN_TABLETOCSV
      (

         @TABLEVALUE TABLEVALUES READONLY

      )

     RETURNS NVARCHAR(500)

     AS
     BEGIN

       DECLARE @CSVSTRING NVARCHAR(500)

       SELECT @CSVSTRING= COALESCE(CONVERT(NVARCHAR(10),@CSVSTRING)+ ', ', '') + VALUE1        FROM @TABLEVALUE

       RETURN @CSVSTRING

     END

 

Step3 

Now you can consume the function in your queries/SP

Declare the variable for the type Tablevalue

 

                  DECLARE @TEMPEMP TABLEVALUES

Insert the value to the tablevalue variable

 

                 INSERT INTO @TEMPEMP

                 SELECT EMPLOYEEID FROM EMPLOYEES

 

Now You can use the select statement with the function call

                   SELECT DBO.UFN_TABLETOCSV (@TEMPEMP)

 

　

　

 

Alternate Method: In the previous method we may encounter performance issue as we are calling the function at each row. We can overcome this using the following method.


You can use the XML path with the select Query to get the results in CSV format


Select DepartmentId ,Convert(nvarchar(Max),( select [EmployeeId]+',' from EmployeeTable ET 
Where ET.DepartmentId=Dep.DepartmentId for xml path(''), type)) as Employees From Department Dep