Concatenate values of a column to display in a row (also use of cross apply)

Hi Everyone,
i will be demonstrating how to use xml path to concatenate column values into a single value. we will also use cross apply to get multiple values(more than one column) to be selected in a select statement.

For demonstrating the above, lets consider the following scenario

1. i have a few boxes
2. i have a few gifts (some new, some old ... ahm... age old regifting)

i need to record information about each box, need to store data about each gift ( also an indicator marking if it is a new gift or an unwanted old gift that i want to give away) . i also need to store information about which box holds which gifts.

lets look at the structure of the tables

CREATE TABLE MyBoxes
(
BoxID INT IDENTITY(1,1) CONSTRAINT MyBoxes_BoxID PRIMARY KEY
, BoxName VARCHAR(20) NOT NULL
)
GO
CREATE TABLE MyGifts
(
GiftID INT IDENTITY(1,1) CONSTRAINT MyGifts_GiftID PRIMARY KEY
, GiftName VARCHAR(50) NOT NULL
)
GO

now lets populate these two tables with some data

INSERT INTO MyBoxes(BoxName)
SELECT TOP 10000 'B' + CAST(ROW_NUMBER() OVER(ORDER BY C1.NAME) AS VARCHAR(20))
FROM sys.columns C1
CROSS JOIN sys.columns C2

INSERT INTO MyGifts(GiftName)
VALUES('G1'), ('G2'), ('G3'), ('G4'), ('G5')

The above statements would have inserted 1000 boxes into MyBoxes table and 5 gift types into MyGifts table.

Now we need a table to store information on which gift (of a perticular type) is strored in which box and also if the gift is new or not.

CREATE TABLE BoxedGifts
(
BoxID INT
CONSTRAINT FK_BoxedGifts_BoxID_MyBoxes_BoxID
FOREIGN KEY REFERENCES MyBoxes(BoxID)
, GiftID INT
CONSTRAINT FK_BoxedGifts_GiftID_MyGifts_GiftID
FOREIGN KEY REFERENCES MyGifts(GiftID)
, NewGift BIT
, CONSTRAINT PK_BoxedGifts
PRIMARY KEY
(
BoxID
, GiftID
)
)

lets populate the above table with some data

INSERT INTO BoxedGifts(BoxID, GiftID, NewGift)
SELECT B.BoxID, G.GiftID, G.NewGift
FROM MyBoxes B
CROSS APPLY
(
SELECT TOP (B.BoxID % 5) GiftID, CAST( (GiftID % 3) AS BIT) AS NewGift
FROM MyGifts
) G

i have used "cross apply" and "top" to put gift items in such a way that for every five boxes, the pattern of type and number of gifts will repeat.

Now i need to get back the information in the following way:
i want to display each box along with the new gifts and old gifts i have put in it.

this can be accomplished in the following way :

SELECT BoxID
, ISNULL((
SELECT CAST(GiftID AS VARCHAR(10)) + ','
FROM BoxedGifts BG
WHERE
BG.BoxID = B.BoxID
AND NewGift = 0
FOR XML PATH('')
), '') AS OldGifts
, ISNULL((
SELECT CAST(GiftID AS VARCHAR(10)) + ','
FROM BoxedGifts BG
WHERE
BG.BoxID = B.BoxID
AND NewGift = 1
FOR XML PATH('')
), '') AS NewGifts

FROM MyBoxes B

the above query will give us the required output. lets now look at how the same can be accomplished using a cross apply.

SELECT BoxID
, G.OldGifts
, G.NewGifts
FROM MyBoxes B
CROSS APPLY (
SELECT ISNULL((
SELECT CAST(GiftID AS VARCHAR(10)) + ','
FROM BoxedGifts BG
WHERE
BG.BoxID = B.BoxID
AND NewGift = 0
FOR XML PATH('')
), '') AS OldGifts
, ISNULL((
SELECT CAST(GiftID AS VARCHAR(10)) + ','
FROM BoxedGifts BG
WHERE
BG.BoxID = B.BoxID
AND NewGift = 1
FOR XML PATH('')
), '') AS NewGifts
) G

result of running above queries can be seen in the following image:

hope you find this article informative.

Use of Rank and/or Cross Apply to get top n items from an ordered grouping

If you want to get the max or min (or an other accumulating function) out of a grouping, a simple group by clause is more than enough.
Lets consider a scenario where you store orders in a table for different products and you want to get the top two orders for each product based on the quantity. For doing this we can't rely upon simple group by query. To accomplish this task, we can either use a ranking function with a common table expression or a statement with cross apply. For the rest of the article we will be looking at applying these two techniques (performance stats of each technique is also provided).

As usual lets start by creating our test tables and populating them with test data.
We will create two tables
1. Products (which will hold info about different products)
2. Orders (which will hold info about all the orders placed for products)

Tables are created using the following scripts

CREATE TABLE Products
(
ProductID INT IDENTITY(1,1) PRIMARY KEY
, ProductName VARCHAR(50)
)

CREATE TABLE Orders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT NOT NULL FOREIGN KEY REFERENCES Products(ProductID),
Quantity INT NOT NULL
)

Having created our tables, its time to populate them with test data using the following scripts

INSERT INTO Products(ProductName)
VALUES('P1'), ('P2'), ('P3'), ('P4'), ('P5'),('P6'), ('P7'), ('P8'), ('P9'), ('P10')

INSERT INTO Orders(ProductID, Quantity)
SELECT ((ROW_NUMBER() OVER( ORDER BY c1.column_ID)) % 10)+ 1 , ROW_NUMBER() OVER( ORDER BY C2.column_ID)
FROM sys.columns c1, sys.columns c2

Now lets create an index on our Orders table to make our queries run faster

CREATE INDEX IDX_Orders_ProductID_Quantity ON Orders( ProductID ASC, Quantity ASC)

First Method
Lets look at using Rank() function

RANK () : Returns the rank of a function with in the partition of a result set

Syntax :
RANK () OVER ( [ ] )

Our ranking query looks like this

;WITH ProductOrders AS
(
SELECT P.ProductName, O.OrderID, O.Quantity, RANK() OVER(PARTITION BY O.ProductID ORDER BY O.Quantity DESC) AS OrderRank
FROM Products P
INNER JOIN Orders O
ON P.ProductID = O.ProductID
)

SELECT *
FROM ProductOrders
WHERE OrderRank < 3

I also managed to capture some stats (io and time) data

(20 row(s) affected)
Table 'Orders'. Scan count 10, logical reads 486, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 217 ms.



Second Method
CROSS APPLY : The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function

Query using CROSS APPLY

SELECT P.ProductName, O.OrderID, O.Quantity
FROM Products P
CROSS APPLY ( SELECT TOP 2 *
FROM Orders
WHERE
ProductID = p.ProductID
ORDER BY Quantity DESC
) O

Stats for this query are as follows:


(20 row(s) affected)
Table 'Orders'. Scan count 10, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.



Both methods described above give us the required output, but using the second method with cross apply seems to be much more efficient.

As usual i hope you find this article interesting. Any comments, questions and suggestion are more than welcome.