April 9, 2009 at 5:52 am
you could determine the max number of parts (e.g. 3) and write a left join statement to concatenate the 3 columns.
SELECT T1.FileData + coalesce(T2.FileData, '') + coalesce(T3.FileData, '')
from DataTable T1
left join DataTable T2
on T1.idcol = T2.idcol
and T1.partno = 1
and T2.partno = 2
left join DataTable T3
on T1.idcol = T2.idcol
and T1.partno = 1
and T3.partno = 3
where T1.partno = 1
I'll have to check if that works with binary data :Whistling:
-- added --
declare @t table (ColA int not null, ColB varbinary(max) not null, ColC int not null default 1)
insert @t
select 1, convert(varbinary(max),'0x123'), 1
union all select 1, convert(varbinary(max),'0x456'), 2
union all select 1, convert(varbinary(max),'0x789'), 3
union all select 2, convert(varbinary(max),'0x0D'), 1
union all select 5, convert(varbinary(max),'0x0E'), 1
union all select 6, convert(varbinary(max),'0x0F'), 1
union all select 7, convert(varbinary(max),'0x0G'), 1
union all select 7, convert(varbinary(max),'0x0H'), 2
union all select 9, convert(varbinary(max),'0x0AA'), 1
select T1.ColA
, convert(varbinary(max), (convert(varchar(max),T1.ColB)
+ coalesce( substring( convert( varchar(max),T2.ColB ) , 3, datalength( convert( varchar(max),T2.ColB ) ) ) ,'')
+ coalesce( substring( convert( varchar(max),T3.ColB ) , 3, datalength( convert( varchar(max),T3.ColB ) ) ) ,''))) as Concatenated_result
, convert(varchar(max),T1.ColB)
+ coalesce( substring( convert( varchar(max),T2.ColB ) , 3, datalength( convert( varchar(max),T2.ColB ) ) ) ,'')
+ coalesce( substring( convert( varchar(max),T3.ColB ) , 3, datalength( convert( varchar(max),T3.ColB ) ) ) ,'') as Concatenated_result_varchar
from @t T1
left join @t T2
on T2.ColA = T1.ColA
and T2.ColC = 2
and T1.ColC = 1
left join @t T3
on T3.ColA = T1.ColA
and T3.ColC = 3
and T1.ColC = 1
where T1.ColC = 1
Or use one of Jeff Modens great string functions at SSC
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 9, 2009 at 7:07 am
Thank you!
You saved my day 🙂
April 9, 2009 at 7:17 am
HTH
Just make sure you test it (so you get to see the lovely pictures as desired) 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply