SQL server query lines output -

when execute following sql query:

select nodename, message messages, messages '%hit%' 

i following output: (string field):

oracle01 file system /apl/oracleasm hit 93% usage          file system /apl/oraclears hit 96% usage          file system /apl/oracleadv hit 97% usage 

is there way change query output below ?

oracle01 file system /apl/oracleasm hit 93% usage oracle01 file system /apl/oraclears hit 96% usage oracle01 file system /apl/oracleadv hit 97% usage 

try 1 -

query:

declare @messages table (       nodename varchar(50)     , [message] varchar(100) )  insert @messages (nodename, [message]) values  ('oracle01', 'file system /apl/oracleasm hit 93% usage'), ('',         'file system /apl/oraclears hit 96% usage'), ('',         'file system /apl/oracleadv hit 97% usage'), ('oracle02', 'file system /apl/oracleadv hit 99% usage'), ('',         'file system /apl/oracleadv hit 80% usage')  ;with cte  (     select            m.nodename         , m.[message]         , rowid = row_number() on (order (select 1))     @messages m     m.[message] '%hit%'  ) select       nodename =          case when isnull(m.nodename, '') = ''              (                 select top 1 m2.nodename                  cte m2                 m2.rowid - 1 < m.rowid                     , isnull(m2.nodename, '') != ''                 order m2.rowid desc             )              else m.nodename          end     , [message] cte m 

output:

nodename    message ----------- ------------------------------------------ oracle01    file system /apl/oracleasm hit 93% usage oracle01    file system /apl/oraclears hit 96% usage oracle01    file system /apl/oracleadv hit 97% usage oracle02    file system /apl/oracleadv hit 99% usage oracle02    file system /apl/oracleadv hit 80% usage 

update:

declare @messages table (       nodename varchar(50)     , [message] varchar(500) )  insert @messages (nodename, [message]) select 'oracle01',  'file system /apl/oracleasm hit 93% usage file system /apl/oraclears hit 96% usage file system /apl/oracleadv hit 97% usage'  select nodename + ' ' + replace([message], char(10), nodename + ' ') @messages 

output update:

oracle01 file system /apl/oracleasm hit 93% usage oracle01 file system /apl/oraclears hit 96% usage oracle01 file system /apl/oracleadv hit 97% usage 

update comment:

select errormessage =       n.caption + ' ' +      n.ambiente + ' ' +      replace(ccs.errormessage, char(10), n.caption + ' ' + n.ambiente + ' ')  dbo.apm_currentcomponentstatus ccs (nolock)  join dbo.apm_application (nolock) on ccs.applicationid = a.id  join dbo.nodes n (nolock) on a.nodeid = n.nodeid  ccs.errormessage '%hit%'      , n.ambiente in ('homologação', 'desenvolvimento')  

is there way sort output mb available ascending ?

declare @table table  (       nodename varchar(50)     , [message] varchar(500) )  insert @table (nodename, [message]) select 'oracle01',  'fs /bd1/devsdata/logs1 hit 93% usage -> 742 mb available  fs /bd1/devsdata/logs1 hit 98% usage -> 542 mb available  fs /bd1/devsdata/tmp hit 99% usage -> 793 mb available'  select * (     select            d.nodename         , [message] = p.value('(.)[1]', 'varchar(500)')     (         select                t.nodename             , kxml = cast('<r><s>' + replace(t.[message], char(10), '</s>' + '<s>') + '</s></r>' xml)           @table t     ) d     cross apply kxml.nodes('/r/s') t(p) ) d order d.nodename, substring([message], charindex('-> ', [message]) + 3, charindex('mb', [message]) - charindex('-> ', [message]) - 3) 

output:

nodename    message ----------- --------------------------------------------------------- oracle01    fs /bd1/devsdata/logs1 hit 98% usage -> 542 mb available  oracle01    fs /bd1/devsdata/logs1 hit 93% usage -> 742 mb available  oracle01    fs /bd1/devsdata/tmp hit 99% usage -> 793 mb available