T-SQL Convert multiples rows into short SMS text string/message

Question

I'm trying to create a short SMS message and a full formatted email from the same data. How can I convert 2 rows from at able into a SQL string for purposes of the text email.

I have only 4 rows in my table, and I assign them a rating of "Good" or "Bad" using my own criteria. The fully formatted email will show all four rows, but the SMS needs to show just the bad ones.

I'll probably send two emails using sp_send_dbmail and some people might want to use Outlook route the SMS one to their phones vs SMS text, and others will use a different alerting system on their phones and just read the email on their device.

Here's an example of what I'd like to do. The @htmlEmailText is working fine, because it uses the "FOR XML" clause.

if @format = 'EMAIL' 
    begin 
        SET @SMSEmailText = 
            'BizTalk Feed Alert ' + 
            CAST (( 
            SELECT Airline, 'Feed=' + ST.Feed, 'MinutesSinceLastMessage=' + MinutesSinceLastMessage 
               where Rating = 'BAD') 
                ) AS NVARCHAR(MAX) )
                ;
        SET @htmlEmailText =
            N'<H1>BizTalk Feed Alert</H1>' +
            N'<table border="1">' +
            N'<tr>' + 
            N'<th>Rating</th>' +
            N'<th>Airline</th>' +
            N'<th>Feed</th>' +
            N'<th>MinutesSinceLastMessage</th>' +
            N'<th>AlertMinutes</th>' +
            N'<th>LocalDateTimeLastMsgRcvd</th>' +
            N'<th>CountMessageInLastHour</th>' +
            N'<th>AvgCountPerSameHour</th>' +
            CAST ( ( SELECT td = Rating,       '',
                            td = Airline, '',
                            td = ST.Feed, '',
                            td = MinutesSinceLastMessage, '',
                            td = AlertMinutes, '',
                            td = LocalDateTimeLastMsgRcvd, '', 
                            td = CountMessageInLastHour, '', 
                            td = AvgCountPerSameHour, ''
                      from @StatsTable ST 
                      left outer join @CountTable CT on ST.Feed = CT.Feed 
                      left outer join @AvgTable AT on ST.Feed = AT.Feed 
                      FOR XML PATH('tr'), TYPE 
            ) AS NVARCHAR(MAX) ) +
            N'</table>' 
            ;
    end 

Answer 1

You can declare an nvarchar(max) variable to hold the rows, and then concatenate them like this:

DECLARE @BadText nvarchar(max)
SET @BadText = ''
SELECT @BadText = @BadText + Airline + '; Feed=' + ST.Feed + '; ' +  
    'MinutesSinceLastMessage=' + MinutesSinceLastMessage 
WHERE Rating = 'BAD'

SET @SMSEmailText = 
        'BizTalk Feed Alert ' + @BadText 

Now, you'll also need to cast any ints/floats to nvarchars as well, and you'll have to spruce up your formatting to your liking, but that should do it.