SQL server query lines output

Question

When I execute the following sql query:

select nodename, message from messages, where messages like '%hit%'

I get the 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 any way to change my query to get 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

Answer 1

Try this one -

Query:

DECLARE @messages TABLE
(
      nodename VARCHAR(50)
    , [message] VARCHAR(100)
)

INSERT INTO @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 AS 
(
    SELECT 
          m.nodename
        , m.[message]
        , RowID = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM @messages m
    WHERE m.[message] LIKE '%hit%' 
)
SELECT
      nodename = 
        CASE WHEN ISNULL(m.nodename, '') = '' 
            THEN (
                SELECT TOP 1 m2.nodename 
                FROM cte m2
                WHERE m2.RowID - 1 < m.RowID
                    AND ISNULL(m2.nodename, '') != ''
                ORDER BY m2.RowID DESC
            ) 
            ELSE m.nodename 
        END
    , [message]
FROM 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 INTO @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 + ' ')
FROM @messages

Output for 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 for comment:

SELECT errormessage = 

    n.caption + ' ' + 
    n.ambiente + ' ' + 
    REPLACE(ccs.errormessage, CHAR(10), n.caption + ' ' + n.ambiente + ' ')

FROM dbo.APM_CurrentComponentStatus ccs WITH (NOLOCK) 
JOIN dbo.APM_Application a WITH (NOLOCK) ON ccs.ApplicationID = a.ID 
JOIN dbo.Nodes n WITH (NOLOCK) ON a.NodeID = n.NodeID 
WHERE ccs.ErrorMessage LIKE '%hit%' 
    AND n.ambiente IN ('homologação', 'desenvolvimento') 

Is there any way to sort output by MB available ascending ?

DECLARE @table TABLE 
(
      nodename VARCHAR(50)
    , [message] VARCHAR(500)
)

INSERT INTO @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 *
FROM (
    SELECT 
          d.nodename
        , [message] = p.value('(.)[1]', 'VARCHAR(500)')
    FROM (
        SELECT 
              t.nodename
            , kxml = CAST('<r><s>' + REPLACE(t.[message], CHAR(10), '</s>' + '<s>') + '</s></r>' AS XML)  
        FROM @table t
    ) d
    CROSS APPLY kxml.nodes('/r/s') t(p)
) d
ORDER BY 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