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.

Storing Unicode string into VARBINARY (MAX)

I came across an interesting issue while working on a client project a couple of days ago. I thought it might be a good idea to share this information.
We had to populate a “data” field which is of type “varbinary(max)”, the content of which is picked up by sharepoint to index.
We had two types of items

1. Items which have files (these files were directly written to the ‘data’ field in their binary form)
2. Items which didn’t have any files (the ‘data’ field needed to be populated with the title and description of item)

There were no issues with files, but when ‘data’ field was populated with title and description, nothing from that field was getting indexed.
Both the title and description fields where of type ‘nvarchar(max)’.

Let’s look at what happens when you write data of ‘varchar(max)’ and ‘nvarchar(max)’ into the ‘varbinary(max)’ field.

--DECLARING FIELDS TO STORE DATA IN DIFFERENT DATA TYPES
DECLARE @MyUnicodeName NVARCHAR(MAX)
DECLARE @MyName VARCHAR(MAX)
DECLARE @BinaryFieldForUnicode VARBINARY(MAX)
DECLARE @BinaryField VARBINARY(MAX)

--POPULATE NAME VARIABLES
SELECT @MyUnicodeName = N'KHAJA'
SELECT @MyName = 'KHAJA'

--POPULATE BINARY VARIABLES
SELECT @BinaryField = CAST(@MyName AS VARBINARY(MAX))
, @BinaryFieldForUnicode = CAST(@MyUnicodeName AS VARBINARY(MAX))

--DISPLAY BINARY VARIABLES
SELECT @BinaryField "Binary Field"
SELECT @BinaryFieldForUnicode "Binary Unicode Field"

Upon running the above code the following result is returned :


It is evident that it has extra ‘00’ for the unicode data.
If you were to read these fields and write it to a text file (using a program),

	Data in txt file
Binary Field	KHAJA
Binary Unicode Field	K H A J A

Here you can see that the txt file for Binary Unicode field has extra spaces.
Problems/Issues

1. When you write the content to a txt file, a space is appended to each character written. Which makes it not really useful for anything(in addition to the extra storage required to save the data)
2. Full text search fails on the data stored in the varbinary(max) column.

Fix
the fix is simple, just do the following when storing the Unicode string into varbinary(max)

SELECT @BinaryFieldForUnicode = 0xFFFE + CAST(@MyUnicodeName AS VARBINARY(MAX))

Adding the byte order mark 0xFFFE at the start will fix both the issues. If you write data from varbinary(max) field to txt file it will write the proper text without extra space characters and the full text search will pick up proper data to index.

Hope this article was helpful. If you have any questions or comments I will be happy to answer.