SQL Server : Blackbox XML Method

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('Products ') NOT NULL

DROP TABLE Products

GO

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:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Reference: DP(http://dptechnicalblog.blogspot.com/ )

SQL Server : SQL code Formatter Tools

Often while debugging the SQL code written another developer which is not formatted, formatting will help in analysing and debugging the code effectively. Also writting the SQL code with proper formatting improves the readability and is a best practise.

I found couple of online code formatting tools which formats SQL code very good and can be handy.

Tool 1: Instant SQL Formatter
Tool 2: SQLinForm

You may try the above tools next time when you need to format your SQL code.

Reference: DP (http://dptechnicalblog.blogspot.com/ )