Stack Exchange
Need both the lines in one single query
my as statement should be as 'statementmessage' which is header line and both message 1 and message 2 should be in single line by concatenating.
help me if any queries let me know.
           WITH with_stmt_bills as (
                   SELECT 

                       BarStatement_Accounts_AcctMsgText.TextID,
                       BarStatement_Accounts_AcctMsgText.TextSeqID,
                         CASE 
                           WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=0 
                           THEN BarStatement_Accounts_AcctMsgText.TextLine
                           END AS Message1,
                         CASE
                           WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=1
                           THEN BarStatement_Accounts_AcctMsgText.TextLine
                           ELSE ''
                           END AS Message2,
         CONCAT(ISNULL('Message1',''),' ',ISNULL('Message2','')) as StatementMessage,

-----------------------With statement 2 ----------------------------
                     full_stmt_bills as (

                           (SELECT top 1 Message1 FROM with_stmt_bills
                                    WHERE VisitID = internal_table.VisitID
                                    AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
                                    ORDER by TextID desc) as Message1,

                            (SELECT top 1 Message2 FROM with_stmt_bills
                                    WHERE VisitID = internal_table.VisitID
                                    AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
                                    ORDER by TextID desc) as Message2,

using this concat down here displays output as message1 message 2 in column,I actually need output for data stored in message 1 and message 2 
          (select CONCAT(ISNULL('Message1',''),' ',ISNULL('Message2',''))) as StatementMessage,


Do you want this as two columns in a single row or two rows with a single column?

Now we have a column name called 'statement message' , but it shows only half message when i run the query because message 1 and 2 is classified based on textseqID

Is that supposed to answer my question? What does that mean? Helping here should be super simple but there just isn't enough information posted.

CASE
WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=0
THEN BarStatement_Accounts_AcctMsgText.TextLine
END AS Message1,
CASE
WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=1
THEN BarStatement_Accounts_AcctMsgText.TextLine
ELSE ''
END AS Message2,

Please don't put code in the comments. It is part of your question and should be posted in the question.

when i use this query it gives me message 1 and message 2 as words in output
(select CONCAT(ISNULL('Message1',''),' ',ISNULL('Message2',''))) as StatementMessage,but it doesn't describes the data stored in  message1 and 2

Please remember that we can't see your screen, we have no knowledge of your project and no idea what you are trying to do other than what you post...which at this point makes very little sense. Maybe this would be a good place to start. https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Concatenation is done using +:
(SELECT top 1 Message1 + Message2 FROM with_stmt_bills
 WHERE VisitID = internal_table.VisitID
   AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
 ORDER by TextID desc) as Message1and2

Added after the OP was edited:
After you edited your post, the first code block (just a fragment of a CTE) is useless, because you seem to try to concatenate two strings (Message1 and Message2) where at most one of them will not be blank (because TextSeqID cannot be 0and 1 at the same time). Nevertheless, ISNULL('Message1','') will always return the string 'Message1'. For sure you meant ISNULL(Message1,''), but this will not work, because you can't reference the calculated columns directly in other calculated columns.
In your second query fragment, it is not at all clear what full_stmt_bills is. Is it another CTE? No, because the definition only contains 2 values but without a VALUES clause. You seem to define 2 completely different Message1 and Message2 columns. So Whiskey Tango Foxtrott are you trying to concatenate to form a concatenated string? I gave the anser for your second code fragment, for the first (the new) one it would be EITHER Message1 or Message2 so:
WITH with_stmt_bills as (
  SELECT 

    BarStatement_Accounts_AcctMsgText.TextID,
    BarStatement_Accounts_AcctMsgText.TextSeqID,
    CASE 
      WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=0 
      THEN BarStatement_Accounts_AcctMsgText.TextLine
    END AS Message1,
    CASE
      WHEN BarStatement_Accounts_AcctMsgText.TextSeqID=1
      THEN BarStatement_Accounts_AcctMsgText.TextLine
      ELSE ''
    END AS Message2,
    CASE 
      WHEN BarStatement_Accounts_AcctMsgText.TextSeqID IN (0, 1) 
      THEN BarStatement_Accounts_AcctMsgText.TextLine
      ELSE ''
    END AS StatementMessage,


Msg 207, Level 16, State 1,  Line 301
Invalid column name 'StatementMessage'.

Msg 207, Level 16, State 1, Line 301 Invalid column name 'StatementMessage'.         Now it says this error , in the below query i have select distinct and there it is described in  full_stmt_table as full _stmt_bills.statementMessage,

That message points to a part of your query that was not part of your post and so does not refer to anything that I produced with my answer.

@skumar Just edited my reply. If you keep insisting to post contradictory code fragments and comment with untraceable error messages, the answers will keep being not satisfactory.

okay where exatcly can  share whole my code .its like 500 lines

You don't have to do that for me, I am not going to go through 500 lines. Just extend your code snippets so we can understand their purpose or describe in what part of your query they are used (cte, expression etc.). And since both parts seem to contain fields named Message1 and Message2, tell us which ones you want to concatenate. Especially for the "invalid column name" error, it would be useful to see where you are using this column (around line 301).

CAN I HAVE YOUR EMAIL TO EXPLAIN PLEASE

When i use the concat string it shows output but is displays as three header lines rather than displaying output combining in same column in one single header row.

So maybe just leave the new column "StatementMessage" and remove the computed columns "Message1" and "Message2". And "No" for the email, sorry.

Here you go!
SELECT
    (SELECT top 1 Message1 FROM with_stmt_bills
     WHERE VisitID = internal_table.VisitID AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
     ORDER by TextID desc)
     + ' ' + 
    (SELECT top 1 Message2 FROM with_stmt_bills
     WHERE VisitID = internal_table.VisitID AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
     ORDER by TextID desc
    ) AS statementmessage

Of course, you could simplify this if you were actually pulling the information from the same row of the same column.
SELECT Message1 + ' ' + Message2 AS statementmessage
FROM with_stmt_bills
WHERE VisitID = internal_table.VisitID AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
ORDER by TextID desc


Don't really need to use two queries if this is what they are trying to do.

Very true.  Possibly the queries were simplified and the questioner copy / pasted the same one.

Msg 207, Level 16, State 1, Line 301
Invalid column name 'StatementMessage'.

Perchance are you using a case sensitive collation method?
