Often its needed for reporting purpose to summarize the normalized data into groups or list of values. This is also known as row concatenation. This can be best done using some reporting and client side tools which support this functionality directly. Using SQL Server also we can do this in different ways.
IF OBJECT_ID('Departments') NOT NULL
DROP TABLE Departments
GO
CREATE TABLE Products (
PROD_ID INT PRIMARY KEY,
PRODUCT_DESC VARCHAR(50));
CREATE TABLE Departments (
DEPT_ID INT PRIMARY KEY,
DEPT_TITLE VARCHAR(50));
CREATE TABLE DepartmentProducts (
DEPT_ID INT
REFERENCES Departments (DEPT_ID),
PROD_ID INT
REFERENCES Products (PROD_ID),
PRIMARY KEY (DEPT_ID,PROD_ID));
INSERT INTO Products VALUES (1, 'Book');
INSERT INTO Products VALUES (2, 'Magazine');
INSERT INTO Products VALUES (3, 'DVD');
INSERT INTO Products VALUES (4, 'Video');
INSERT INTO Products VALUES (5, 'CD');
INSERT INTO Products VALUES (6, 'Map');
INSERT INTO Departments VALUES (1, 'Reference');
INSERT INTO Departments VALUES (2, 'Periodicals');
INSERT INTO Departments VALUES (3, 'Entertainment');
INSERT INTO DepartmentProducts VALUES (1, 1);
INSERT INTO DepartmentProducts VALUES (1, 6);
INSERT INTO DepartmentProducts VALUES (2, 2);
INSERT INTO DepartmentProducts VALUES (3, 3);
INSERT INTO DepartmentProducts VALUES (3, 4);
INSERT INTO DepartmentProducts VALUES (3, 5);
The Blackbox XML methods:
--Using CTE
WITH cteProdDesc(DEPT_ID,PROD_DESC)
AS
(
SELECT DEPT_ID,PRODUCT_DESC
FROM DepartmentProducts AS DP
INNER JOIN Products AS P ON P.PROD_ID= DP.PROD_ID
WHERE DP.DEPT_ID = D.DEPT_ID
)
SELECT D.DEPT_ID,
D.DEPT_TITLE,STUFF((SELECT ',' + cpd.PRODUCT_DESC
FROM Departments D
INNER JOIN cteProdDesc cpd ON cpd.DEPT_ID = D.DEPT_ID
ORDER BY PRODUCT_DESC FOR XMLPATH('')),1,1,'') AS PRODUCT_LIST
FROM Departments D
GO
-- Using correlated subquery
SELECT D.DEPT_ID,
D.DEPT_TITLE,
STUFF((SELECT ',' + PRODUCT_DESC
FROM DepartmentProducts AS DP
INNER JOIN Products AS P ON P.PROD_ID= DP.PROD_ID
WHERE DP.DEPT_ID = D.DEPT_ID
ORDER BY PRODUCT_DESC
FOR XML PATH('')), 1, 1, '') AS PRODUCT_LIST
FROM Departments AS D;
GO
-- Using CROSS APPLY
SELECT D.DEPT_ID,
D.DEPT_TITLE,
STUFF(P.PROD_LIST, 1, 1, '') AS PRODUCT_LIST
FROM Departments AS D
CROSS APPLY (SELECT ',' + PRODUCT_DESC
FROM DepartmentProducts AS DP
INNER JOIN Products AS P ON P.PROD_ID= DP.PROD_ID
WHERE DP.DEPT_ID = D.DEPT_ID
ORDER BY PRODUCT_DESC
FOR XML PATH('')) AS P (PROD_LIST);
GO
-- Results
DEPT_ID DEPT_TITLE PRODUCT_LIST
-------------- ---------------- ------------
1 Reference Book,Map
2 Periodicals Magazine
3 Entertainment CD,DVD,Video
This method is often called blackbox XML method and here is the explanation for this effect using FOR XML clause. Normally the PATH clause use with input string creates the wrapper element in result set. However the PATH clause used with empty string gives result without creating wrapper element. And since the content is retrieved as text, the concatenation effect is achieved.
Resources:
