HOW TO USE COALESCE FUNCTION IN SQL SERVER

COALESCE: The  COALESCE function is used to coalesce may rows into a single row or one row. The coalesce function is also used to handle the NULL values. The basic example of Coalesce function is as below:

Here, we have an Employee table with list of 20 records in TESTDB on my local server. Please see the below screenshot.

So, here we have an EMP_NAME column in which the employee names are displaying in each row individually. So now we will coalesce all names into a single row with the help of Coalesce function in sql server. The changes are as below.

DECLARE @employeeNames VARCHAR(8000)

SELECT @employeeNames = COALESCE(@employeeNames + ' , ', '') + CAST(EMP_NAME AS VARCHAR) FROM   Employee

SELECT @employeeNames 

It will retruns all the employee names into a single row as like this:

RAMAN SACHDEVA , ANAND SACHDEVA , PALKA SACHDEVA , RITESH SINGH , ARIJEET SINGH , TARUN SHARMA , KULWINDER SINGH , KRISHAN KUMAR , RAJINDER KUMAR , ATUL SHARMA , FATEH SINGH , RAJDEEP SINGH , JAGJEET SINGH , ANKUSH KUMAR , ROSHAN SINGH , SAURABH KUMAR , VARINDER SINGH , NITISH KUMAR , YUVRAJ SINGH , HARDEEP KAUR

Please see an above example into the below screenshot for further clarifications.

          - - - - - - Cheers, Happy to Help! - - - - - -