SQL Server Concatenate Row Values using T-SQL

Some time we need to show concatenated row values in the SQL Select Statements. Lets say, if we have Customers, Orders and Order details. Incase, if we have to list out all Products that each customer ordered in a single column.

Here is the snippet.

 DECLARE @OrderDetails TABLE( 
[OrderDetailsID] INT NOT NULL, 
[OrderID] INT NOT NULL, 
[ProductName] [nvarchar](40) NOT NULL, 
[Quantity] INT NOT NULL 
) 


DECLARE @Orders TABLE( 
[OrderID] INT NOT NULL , 
[CustomerID] INT NOT NULL, 
[OrderDate] [datetime] NULL) 


DECLARE @Customers TABLE( 
[CustomerID] INT NOT NULL, 
[CompanyName] [nvarchar](40) NOT NULL, 
[ContactName] [nvarchar](30) NULL 
) 

INSERT INTO @Customers SELECT 1, 'ABC Ltd', 'Guru' 
INSERT INTO @Customers SELECT 2, 'XYZ Ltd', 'Alick' 
INSERT INTO @Customers SELECT 3, 'SSK Ltd', 'James' 
INSERT INTO @Customers SELECT 4, 'RRS Ltd', 'Rohan' 
INSERT INTO @Customers SELECT 5, 'SJFJ Ltd', 'Prince' 

INSERT INTO @Orders SELECT 1, 1, '15 Jan 2015' 
INSERT INTO @Orders SELECT 2, 1, '11 Jan 2013' 
INSERT INTO @Orders SELECT 3, 1, '13 Jan 2011' 
INSERT INTO @Orders SELECT 4, 2, '12 Jan 2014' 
INSERT INTO @Orders SELECT 5, 2, '18 Jan 2015' 

INSERT INTO @OrderDetails SELECT 1, 1, 'Product 1', 10 
INSERT INTO @OrderDetails SELECT 2, 1, 'Product 2', 5 
INSERT INTO @OrderDetails SELECT 3, 2, 'Product 3', 10 
INSERT INTO @OrderDetails SELECT 4, 2, 'Product 1', 5 
INSERT INTO @OrderDetails SELECT 5, 4, 'Product 3', 10 
INSERT INTO @OrderDetails SELECT 6, 5, 'Product 5', 5 

/* 
SELECT * FROM @Customers 
SELECT * FROM @Orders 
SELECT * FROM @OrderDetails 
*/ 

SELECT c.[CompanyName], 
( 
SELECT od.ProductName + ':' + CAST(od.Quantity AS VARCHAR(50)) + ', ' 
FROM @OrderDetails od INNER JOIN 
@Orders o ON od.OrderID = o.OrderID 
WHERE o.CustomerID = c.CustomerID 
FOR XML PATH('') 
) AS Products 
FROM @Customers c 

 

 

Here is the output.

Comments

• Anonymous
  February 05, 2015
  Isn't the XML_PATH going to be depreciated soon?