SELECT [OrderID]
,[ProductID]
,[UnitPrice]
,[Quantity]
,[Discount]
FROM [dbo].[OrderDetails]
WHERE OrderID = CASE WHEN @OrderID IS NOT NULL THEN @OrderID ELSE OrderID END
AND [ProductID] =CASE WHEN @ProductID IS NOT NULL THEN @ProductID ELSE ProductID END

Using COALESCE function , we can concatenate many row values in to one single string. this is one use of COALESCE , there are many ,so you can find some more clicking above mention link.

Example 2: XML PATH

1

2
In second example you can see I used STUFF function . So I used it for remove extra comma from string. Usually STUFF function does inserts a string into another string, it deletes first string character form begin for given length and put second string into it

;WITH CTE_CUSTOMER_EX1AS(SELECT [CustomerID] ,[CompanyName] ,[ContactName] FROM .[dbo].[Customers])SELECT * FROM CTE_CUSTOMER_EX1NoteIf there are query before CTE define , need to use Semicolon teminator before declare CTEMultiple CTE With CTE_M1 as (-select statement) , CTE_M2 as (-select statemet)you can find some more detail and examples using this Link and Link2