mercoledì 1 maggio 2013

TSQL - Xml path to concatenate

We could be in need of concatenate in a single string the contents of many columns and row in a table. Let's have a var table like that
DECLARE @TABLE table(id int identity(1,1),
     title varchar(50), textVal varchar(50))
insert into @TABLE
   select 'TitleOne', 'First Text'
   union all
   select '2', 'Text number two'
   union all
   select 'Three', ''
   union all
   select null, null
   union all
   select 'Row5', 'value 5'
   union all
   select 'NullOnlyValue', null
select * from @TABLE
id
title
textVal
1 TitleOne First Text
2 2 Text number two
3 Three
4 NULL NULL
5 Row5 value 5
6 NullOnlyValue NULL

and assume we could need to have a result like
title: textVal; ....
and so one for every row we can use for xml path('') to concatenate the fields as we need...
select '; '+ title +': '''+ isnull(textVal, '') +'''' from @TABLE for xml path ('')
then use stuff function to have the result without the first ;
select stuff((
   select '; '+ title +': '''+ isnull(textVal, '') +'''' from @TABLE for xml path ('')
   ), 1,1, '') concatenatedString
this will produce this expected result
-- TitleOne: 'First Text'; 2: 'Text number two'; Three: ''; NullOnlyValue: ''; Row5: 'value 5'

Nessun commento:

Posta un commento