NetInverse Developers Blog

COALESCE (Transact-SQL)

Returns the first nonnull expression among its arguments. (From MSDN)

COALESCE is essentially same as below:

            CASE
               WHEN (expression1 IS NOT NULL) THEN expression1
               ...
               WHEN (expressionN IS NOT NULL) THEN expressionN
               ELSE NULL
            END

Example:

In this sample, we use COALESCE to generate a flat list. You can specify a delimiter to seperate the items in the list.

            CREATE TABLE [dbo].[student](
                [id] [int] NOT NULL,
                [name] [nvarchar](50),
                [ssn] [varchar](11),
             CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
            (
                [id] ASC
            )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
            ) ON [PRIMARY]
            GO

            INSERT student VALUES(1, 'andrew', '201-98-9238')
            INSERT student VALUES(2, 'lindsay', '656-89-9238')
            INSERT student VALUES(3, 'david', '555-22-1111')
            GO

            DECLARE @list varchar(1024)
            SELECT @list = COALESCE(@list + '|', '') + name
            FROM student

            PRINT @list

Here is the result set:

            andrew|lindsay|david