SQL: Use COALESCE (Transact-SQL) to generate a list

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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                [ssn] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
             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

See also

THIS POST IS PROVIDED "AS-IS" WITH NO WARRANTIES AND CONFERS NO RIGHTS. Build time: Sun 03/30/2008 . ©2007 Dalun Software. All rights reserved. Back to Article List