|
The following
Microsoft SQL Server T-SQL sample scripts demonstrate the usage of COALESCE for list generation:
USE AdventureWorks2008;
DECLARE @ColorList varchar(100)
SELECT @ColorList = COALESCE(@ColorList + ', ', '') + Color
FROM (SELECT DISTINCT Color FROM Production.Product
WHERE Color is not NULL ) x
SELECT ProductColors = @ColorList
GO
/* ProductColors
Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow
*/
USE pubs
GO
DECLARE @Publishers VARCHAR(4096)
SELECT @Publishers = COALESCE(@Publishers + ', ','') + pub_name
FROM publishers
SELECT Publishers = @Publishers
GO
/*
Publishers
New Moon Books, Binnet & Hardley, Algodata Infosystems, Five Lakes Publishing,
Ramona Publishers, GGG&G, Scootney Books, Lucerne Publishing
*/
|
