DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Coalesce SQL Server

09.29.2008
| 9889 views |
  • submit to reddit
        COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value

SELECT FIRSTNAME + ' ' +COALESCE(MIDDLENAME,'')+ ' ' +COALESCE(LASTNAME,'') AS 'FULL NAME', 
FIRSTNAME, 
MIDDLENAME, 
LASTNAME 
FROM CONTACT

<pre>
FULL NAME                                                                                                                                                FIRSTNAME                                          MIDDLENAME                                         LASTNAME                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
Syed  Abbas                                                                                                                                              Syed                                               NULL                                               Abbas
Catherine R. Abel                                                                                                                                        Catherine                                          R.                                                 Abel
Kim  Abercrombie                                                                                                                                         Kim                                                NULL                                               Abercrombie
Humberto  Acevedo                                                                                                                                        Humberto                                           NULL                                               Acevedo
Pilar  Ackerman                                                                                                                                          Pilar                                              NULL                                               Ackerman
Frances B. Adams                                                                                                                                         Frances                                            B.                                                 Adams
Margaret J. Adams                                                                                                                                        Margaret                                           J.                                                 Adams
Carla J. Adams                                                                                                                                           Carla                                              J.                                                 Adams
Jay  Adams                                                                                                                                               Jay                                                NULL                                               Adams
Ronald L. Adina                                                                                                                                          Ronald                                             L.                                                 Adina

</pre>
Comma separated list
declare @emp varchar(100);
select top 10 * from employee order by employeeid
select top 10 @emp = coalesce(@emp + ', ', '') + cast(employeeid as varchar(5)) from employee order by employeeid
select @emp

<pre>
EmployeeID  FirstName                                          AddressID   ShiftID LastName                                           MiddleName                                         NameStyle NationalIDNumber Title                                              HireDate                                               BirthDate                                              LoginID                                                                                                                                                                                                                                                          EmailAddress                                       DepartmentID MaritalStatus EmergencyContactName                               EmergencyContactPhone     SalariedFlag Gender ManagerID   PayFrequency BaseRate              VacationHours SickLeaveHours SalesPersonFlag CurrentFlag ModifiedDate                                           rowguid                              
----------- -------------------------------------------------- ----------- ------- -------------------------------------------------- -------------------------------------------------- --------- ---------------- -------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------ ------------- -------------------------------------------------- ------------------------- ------------ ------ ----------- ------------ --------------------- ------------- -------------- --------------- ----------- ------------------------------------------------------ ------------------------------------ 
1           Terri                                              1           1       Duffy                                              Lee                                                0         245797967        VP Engineering                                     1998-03-03 00:00:00.000                                1961-09-01 00:00:00.000                                adventure-works\Terri                                                                                                                                                                                                                                            Terri@adventure-works.com                          1            S             Tad Orman                                          586-883-8338              1            F      300         2            63.4615               1             20             0               1           2003-01-15 19:26:13.900                                1B76E019-0D60-4D48-B405-C9144FB9B3AB
2           Jian Shuo                                          2           1       Wang                                               NULL                                               0         509647174        Engineering Manager                                1997-12-12 00:00:00.000                                1964-12-13 00:00:00.000                                adventure-works\Jian                                                                                                                                                                                                                                             Jian@adventure-works.com                           1            M             Qiang Wang                                         249-433-7659              1            M      1           2            43.2692               2             21             0               1           2003-01-15 19:26:13.900                                69C8C27C-87DF-45B4-9A46-AB603268AB1B
3           Michael                                            3           1       Sullivan                                           I                                                  0         42487730         Sr. Design Engineer                                2001-01-30 00:00:00.000                                1969-07-17 00:00:00.000                                adventure-works\Michael6                                                                                                                                                                                                                                         Michael6@adventure-works.com                       1            S             Andy Teal                                          766-064-3448              1            M      2           2            36.0577               3             21             0               1           2003-01-15 19:26:13.900                                B219E6A5-322A-4FB5-AEEC-2541F9E8EBAA
4           Sharon                                             4           1       Salavaria                                          B                                                  0         56920285         Design Engineer                                    2001-02-18 00:00:00.000                                1951-06-03 00:00:00.000                                adventure-works\Sharon1                                                                                                                                                                                                                                          Sharon1@adventure-works.com                        1            M             Jeff Pike                                          870-847-2433              1            F      2           2            32.6923               4             22             0               1           2003-01-15 19:26:13.900                                5869AD03-A3B3-4B3D-8E23-063E1BECEB76
5           Gail                                               5           1       Erickson                                           A                                                  0         695256908        Design Engineer                                    1998-02-06 00:00:00.000                                1942-10-29 00:00:00.000                                adventure-works\Gail                                                                                                                                                                                                                                             Gail@adventure-works.com                           1            M             Gregory Erickson                                   474-111-9969              1            F      2           2            32.6923               5             22             0               1           2003-01-15 19:26:13.900                                667D7156-8356-4F3A-84ED-2C84571FBDC9
6           Jossef                                             6           1       Goldberg                                           H                                                  0         998320692        Design Engineer                                    1998-02-24 00:00:00.000                                1949-04-11 00:00:00.000                                adventure-works\Jossef                                                                                                                                                                                                                                           Jossef@adventure-works.com                         1            M             Kathie Flood                                       339-955-0789              1            F      2           2            32.6923               6             23             0               1           2003-01-15 19:26:13.900                                153E1CB7-5AF4-4C43-AA57-1B6014B2B0C8
7           Ovidiu                                             7           1       Crãcium                                            V                                                  0         974026903        Sr. Tool Designer                                  2001-01-05 00:00:00.000                                1968-02-18 00:00:00.000                                adventure-works\Ovidiu                                                                                                                                                                                                                                           Ovidiu@adventure-works.com                         2            S             Luca Dellamore                                     565-356-8092              0            F      2           2            28.8462               7             23             0               1           2003-01-15 19:26:13.900                                EBF6EC63-AFBC-4FD0-BBD8-79FDFFC92420
8           Janice                                             8           1       Galvin                                             M                                                  0         486228782        Tool Designer                                      2001-01-23 00:00:00.000                                1979-06-29 00:00:00.000                                adventure-works\Janice                                                                                                                                                                                                                                           Janice@adventure-works.com                         2            M             Walter Cavestany                                   171-234-7963              0            F      7           2            25.0000               8             24             0               1           2003-01-15 19:26:13.900                                A9A8ECCF-6058-4112-8E62-6D8A7B8CF01D
9           Thierry                                            9           1       D'Hers                                             B                                                  0         480168528        Tool Designer                                      1998-01-11 00:00:00.000                                1949-08-29 00:00:00.000                                adventure-works\Thierry                                                                                                                                                                                                                                          Thierry@adventure-works.com                        2            M             Beverly Baker                                      214-368-8065              0            M      7           2            25.0000               9             24             0               1           2003-01-15 19:26:13.900                                CF5A6683-DD71-4EA1-9EDF-E2D85F03D1BD
10          Brian                                              10          1       Welcker                                            S                                                  0         112432117        VP Sales                                           2001-03-18 00:00:00.000                                1967-07-08 00:00:00.000                                adventure-works\Brian4                                                                                                                                                                                                                                           Brian4@adventure-works.com                         3            S             Erin Hagens                                        203-175-2141              1            M      299         2            72.1154               10            25             0               1           2003-01-15 19:26:13.900                                39ADABF3-6A6A-4EB0-BCEB-8FF834F4DE24

(10 row(s) affected)

                                                                                                     
---------------------------------------------------------------------------------------------------- 
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

(1 row(s) affected)


</pre>