December 4, 2008 at 7:54 am
In a recent post Ken Simmons showed us how to concatenate column data using FOR XML PATH
http://www.sqlservercentral.com/Forums/Topic612520-338-1.aspx
Ken Simmons (12/2/2008)
Something like this.--Sample Data
Declare @Tbl Table (RoomID int)
Insert INTO @Tbl
Select 101
Union All
Select 102
Union All
Select 201
Union All
Select 202
Union All
Select 203
--Sample Query
SELECT
Distinct
RoomList = substring((SELECT ( ', ' + Cast(RoomID as varchar(50)))
FROM @tbl t2
WHERE Substring(Cast(t1.RoomID as varchar(50)),1,1) =
Substring(Cast(t2.RoomID as varchar(50)),1,1)
FOR XML PATH( '' ) )
, 3, 1000 )FROM @Tbl t1
Here is a link with more examples.
I want to concatenate binary column data in a similar way.
e.g. If I have (simplistically)
SELECT CAST(0x4142434445464748 AS VARBINARY(8) ) AS BinData
INTO #TmpBin
UNION
SELECT CAST(0x6162636465666768 AS VARBINARY(8))
And I want to concatenate the contents of the BinData column into one varbinary field.
The expected output would be 0x41424344454647486162636465666768
SELECT ( Convert(varbinary(max), BinData )
FROM #TmpBin
FOR XML PATH ( '' )
This does not give the required result, but converts the result to text. If I convert that result to varbinary - I get something altogether unexepected
SELECT Convert(varbinary(max), (
SELECT ( Convert(varbinary(max), BinData )
FROM #TmpBin
FOR XML PATH ( '' )
) )
This gives
0x4100420043004400450046004700480061006200630064006500660067006800
It looks as if the original binary was converted to unicode text, then converted back to binary.
Is there another solution to the concatenation problem that would work with binary data?
December 4, 2008 at 9:45 am
you said your example was a simplication, but why can't you just use the + operator?
select CAST(0x4142434445464748 AS VARBINARY(8) )
+ CAST(0x6162636465666768 AS VARBINARY(8)) as result
result: 0x41424344454647486162636465666768
using xml to convert to csv for external consumption is fine, but if you're going to stuff the concatenated binary value back into the db, a pure sql route may be better. here's an example.
declare @bin_data table (category int, data varbinary(8))
insert @bin_data (category, data)
SELECT 1, CAST(0x4142434445464748 AS VARBINARY(8) )
union SELECT 1, CAST(0x6162636465666768 AS VARBINARY(8))
union SELECT 1, CAST(0x4162636465666768 AS VARBINARY(8))
union SELECT 2, CAST(0x1162636465666768 AS VARBINARY(8))
union SELECT 2, CAST(0x2162636465666768 AS VARBINARY(8))
union SELECT 3, CAST(0x3162636465666768 AS VARBINARY(8))
select * from @bin_data
declare @seq int, @Last int, @cat int, @all_data varbinary(max)
declare @results table (category int, all_data varbinary(max), seq int identity,
primary key (seq))
insert @results (category)
select distinct category from @bin_data
set @Last = @@rowcount
set @seq = 1
while @seq <= @Last
begin
select @cat = category from @results where seq = @seq
set @all_data = cast(0xFF as varbinary(2))
select @all_data = @all_data + data from @bin_data where category = @cat
update @results set all_data = @all_data where seq = @seq
set @seq = @seq + 1
end
select * from @results
December 4, 2008 at 3:17 pm
I did use the + operator in the end
CREATE FUNCTION dbo.fn_ConcatBin(@pKey AS INT)
RETURNS VARBINARY(MAX)
AS
BEGIN
DECLARE @VAR varbinary(MAX);
SET @VAR = 0x;
SELECT @VAR = @VAR + convert(varbinary(max), fValue)
FROM pcvBlock AS B
WHERE B.fk_BlockRef = @pKey
ORDER BY B.sequence
RETURN @VAR;
END
Not ideal as the function contains a specific table reference, which I ususally like to avoid, but I couldn't find any way around it as the varbinary was needed in another resultset
SELECT
BR.pKey, BR.FileNo, BR.BlockNo, BR.StartRef, BR.EndRef, BR.ItemCount,
BlockData = dbo.fn_ConcatBin(BR.pKey)
FROM pcvBlockRef AS BR
WHERE pKey <= 40
Here the resulting BlockData is over 8000 bytes long, and contains around 1000 fValues.
If I could have got the XML PATH version to work it would have been more understandable - something like this (which doesn't work)
SELECT
BR.pKey, BR.FileNo, BR.BlockNo, BR.StartRef, BR.EndRef, BR.ItemCount,
BlockData = (
SELECT ( Convert(varbinary(max), fValue) )
FROM pcvBlock AS B
WHERE BR.pKey = B.fk_BlockRef
ORDER BY B.sequence
FOR XML PATH ('')
)
FROM pcvBlockRef AS BR
WHERE pKey <= 40
December 5, 2008 at 10:59 am
Tom,
I kept fiddling with this and came up the following:
declare @bin_data table (category int, data varbinary(8))
insert @bin_data (category, data)
SELECT 1, CAST(0x2142434445464748 AS VARBINARY(8) )
union SELECT 1, CAST(0x3162636465666768 AS VARBINARY(8))
union SELECT 1, CAST(0x2262636465666768 AS VARBINARY(8))
union SELECT 2, CAST(0x2142434445464748 AS VARBINARY(8))
union SELECT 2, CAST(0x3162636465666768 AS VARBINARY(8))
union SELECT 3, CAST(0x3162636465666768 AS VARBINARY(8))
select A.category, cast(cast((select cast(data as varchar(255))
from @bin_data B
where B.category = A.category
for xml path('')) as varchar) as varbinary) as all_data
from (select distinct category from @bin_data) as A
result:
[font="Courier New"]
10x214243444546474822626364656667683162636465666768
20x21424344454647483162636465666768
30x3162636465666768[/font]
For some reason, the string from the XML query has to be converted to varchar and then to varbinary. If the outer cast to varchar is omitted, the varbinary data is incorrect.
So, this query should work with the example in your prior posting.
SELECT
BR.pKey, BR.FileNo, BR.BlockNo, BR.StartRef, BR.EndRef, BR.ItemCount,
cast(
cast(
(select cast(fValue as varchar(255))
from pcvBlock B
where B.fk_BlockRef = BR.pKey
for xml path(''))
as varchar)
as varbinary) as BlockData
FROM pcvBlockRef AS BR
WHERE pKey <= 40
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply