Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
create table #t
(a int
,b nvarchar(20)
)
go
insert into #t values(1,'a&b')
insert into #t values(1,'c<
d')
insert into #t values(2,'wx')
insert into #t values(2,'yz')
go
select a,b,
(select b+N''
from #t
where a=t.a
for xml path('')
) 'WITH xml CHARACTERS'
,replace(
replace(
replace(
replace(
replace(
replace(
replace(
(select b+N''
from #t
where a=t.a
for xml path('')
)
,' ',char(20))
,'&','&')
,'<','<')
,'>','>')
,'
',char(10))
,'
',char(13))
,'	',char(9)) 'With XML Replaced'
from #t t
go
drop table #t
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
select o.name as oname,(select c.name + ',' from sys.columns c where o.object_id=c.object_id for xml path('')) cnames
from sys.objects o
where o.name like '#BG_Objects%'
go
drop table #BG_Objects1
drop table #BG_Objects2
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
;with cte_concat as
(
select (select max(ordinal_position) from information_schema.columns c where c.table_name =i.table_name) max_num,
ordinal_position, convert(varchar(1000),column_name) as column_name, table_name
from information_schema.columns i where table_name like '#bg_objects%' and ordinal_position = 1
union all
select max_num,i.ordinal_position, convert(varchar(1000),rtrim(c.column_name) + ',' + rtrim(i.column_name)), c.table_name
from cte_concat c
inner join information_schema.columns i on c.ordinal_position + 1 = i.ordinal_position and c.table_name = i.table_name
)
select table_name,column_name from cte_concat where ordinal_position = max_num
go
drop table #BG_Objects1
drop table #BG_Objects2
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
;with cte_concat as
(
select (select max(ordinal_position) from information_schema.columns c where c.table_name =i.table_name) max_num,
ordinal_position, convert(varchar(1000),'['+column_name+']') as column_name, table_name
from information_schema.columns i where table_name like '#bg_objects%' and ordinal_position = 1
union all
select max_num,i.ordinal_position, convert(varchar(1000),rtrim(c.column_name) + ',[' + rtrim(i.column_name)+']'), c.table_name
from cte_concat c
inner join information_schema.columns i on c.ordinal_position + 1 = i.ordinal_position and c.table_name = i.table_name
)
select table_name,column_name from cte_concat where ordinal_position = max_num
go
drop table #BG_Objects1
drop table #BG_Objects2
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.