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
Coalesce SQL Server
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>
