Comma Separated Lists

Creating Comma Separated lists from table values is a very common need, and can be done many different ways.  I am referring to selecting values from multiple records into a single, comma-separated value.  See these two examples, referencing the Production.Culture table within AdventureWorks:


  -- Look at the data values

  USE AdventureWorks

  GO

  SELECT [Name]

  FROM Production.Culture
  GO


     Name
    Arabic
    Chinese
    English
    French
    Hebrew
    Invariant Language (Invariant Country)
    Spanish
    Thai

  --Method #1
  SELECT SUBSTRING(
  (SELECT ',' + c.Name
  FROM Production.Culture c
  ORDER BY c.Name
  FOR XML PATH('')),2,200000) [Cultures]
  GO

    Cultures
    Arabic,Chinese,English,French,Hebrew,Invariant Language (Invariant Country),Spanish,Thai


  --Method #2
  DECLARE @List varchar(3000)
  SELECT @List = COALESCE(@List + ', ', '') + [name]
  FROM Production.Culture
  SELECT @List [Cultures] 

    Cultures
    Arabic, Chinese, English, French, Hebrew, Invariant Language (Invariant Country), Spanish, Thai

Many people consider XML as the best solution for performance, but there are also many other ways to approach this.  I will be back soon with some more.

Day of Week Functions

It is often necessary to return the day of the week name, rather than the numeric date.  This is a handy little function for returning the day name, for a given date value:


IF OBJECT_ID('udf_DayOfWeek','FN')>0
DROP FUNCTION dbo.udf_DayOfWeek
GO
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
  DECLARE @rtDayofWeek VARCHAR(10)
  SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO


--Then you can reference the function like this:
  SELECT dbo.udf_DayOfWeek(GETDATE()) [Day Of Week]
  SELECT dbo.udf_DayOfWeek('07/02/2011') [My Next Birthday]


Here's another one that is useful for getting the first day of the week.  This can be very helpful for reporting transactions from the beginning of the week, as opposed to the start of the current day:

IF OBJECT_ID('udf_FirstDayOfWeek','FN')>0

DROP FUNCTION dbo.udf_FirstDayOfWeek

GO

CREATE FUNCTION [dbo].[udf_FirstDayOfWeek] ( 

 @InputDate    DATETIME 

)

RETURNS DATETIME

BEGIN

  SET @InputDate = CONVERT(VARCHAR(10), @InputDate, 111)

  RETURN DATEADD(DD, 1 - DATEPART(DW, @InputDate),@InputDate)

END

GO

--Then you can reference the function like this:

 SELECT dbo.udf_FirstDayOfWeek(GETDATE()) [This Week]

 SELECT dbo.udf_FirstDayOfWeek('07/02/2011') [My Birthday Week]