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.