I’m often ask how combine information on same line in a query. Let me explain, lets say that you would like to list all products from the Production.product table in the AdventureWorks database and that you would like to list ALL vendors for each product from the Purchasing.Vendor table on the same line.
Look at the following example:
You can see in the 3rd column that all venders are list separated by a comma.
In the AdventureWorks database the relation between the Product table and the Vendor table in done through a third table called ProductVendor. Look at the following diagram:
Let’s start with this simple query that uses inner joins to link all three tables:
SELECT Production.Product.Name AS [Product Name],
Production.Product.ProductNumber,
Purchasing.Vendor.Name AS Vendor
FROM Production.Product INNER JOIN Purchasing.ProductVendor
ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID
INNER JOIN Purchasing.Vendor
ON Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID
ORDER BY [Product Name]
When you execute this query, you get the following:
In this example, we get all vendors on separate rows. Not exactly what we want. To solve this issue, we need to create a user defined function that will return all of our rows on a single line.
Execute the script below:
CREATE FUNCTION [dbo].[ufnVendorOnSameLine](@Id int,@Delimiter varchar(10))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @OnSameLine varchar(max)
set @OnSameLine=''
--
--by using SELECT to set the variable, all rows are concatenated together.
SELECT @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter
FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor
ON Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID
WHERE (Purchasing.ProductVendor.ProductID = @Id)
--
--we need to remove the last delimiter
set @OnSameLine = substring(@OnSameLine,1,len(@OnSameLine)-1)
RETURN @OnSameLine
END;
Once we have created the udf, we need to modify the query to remove the Purchasing.Vendor.Name column and replace it with the function. Also, we need to include a DISTINCT since we are unse an inner join to the Purchasing.ProductVendor table and finally, we remove the inner join to the Purchasing.Vendor table:
SELECT DISTINCT Production.Product.Name AS [Product Name],
Production.Product.ProductNumber,
dbo.ufnVendorOnSameLine(ProductVendor.ProductID,', ') AS Vendors
FROM Production.Product INNER JOIN Purchasing.ProductVendor
ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID
ORDER BY [Product Name]
How it Works:
Basicaly, we use a technique that has been around since the early day of MS SQL Sever. In fact prior to version 7, the SET command did not exist. We needed to use the SELECT to assign a value to a variable. For example:
SELECT @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter
and
SET @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter
Are the same…it’s just a long forgotten technique.
Hope this was useful,
Eric Vaillancourt