CODEWITHSUNDEEP
c#.netsql-server-2008t-sqlselect
Worgon
Worgon

Reputation: 1657

T-Sql Select and Concatenate many rows and column into one row and one column

Basically, i wanted to return only one row and one column of result from a stored procedure..

For example, my sample data and table is like this below:

ColoumA INT
ColoumB INT
ColoumC NVARCHAR(MAX)

ColoumA  ColoumB  ColoumC
   1       1      ErrorMessage1
   2       2      ErrorMessage2
   3       3      ErrorMessage3

After i Execute Successfully the stored procedure successfully,If Successful, the return result is a string value that combine all the detail above.Such as

Or in another Word, i wanted to stored all the value in TBL.rows[0][0]

in C#.net when i get call the Code 

String ReturnValue = "";
Datatable tbl = new Datatable(AREA);
ReturnValue = tbl.Rows[0][0].ToString().Trim();
Console.Writeline("Sample Output");
Console.Writeline(ReturnValue);

I Expected something like this:

Sample Output
ColoumA: 1, ColoumB 1, ErrorMessage1
ColoumB: 2, ColoumB 2, ErrorMessage2
ColoumC: 3, ColoumC 3, ErrorMessage3

Am I Able to do the selection using T-Sql Statment? And if can do So? how can i get it?

Hopefully Someone can understand and answer my question.

Thank You:

PS:Sorry For my poor English Explanation

Upvotes: 4

Views: 1129

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062895

My strong recommendation here would be: don't. Access it as a grid (regular SELECT), and do any processing at your app-tier, i.e.

var sb = new StringBuilder();
using(var reader = cmd.ExecuteReader()) {
  while(reader.Read()) {
    sb.Append("ColoumA: ").Append(reader.GetInt32(0))
      .Append(", Coloum B").Append(reader.GetInt32(1))
      .Append(", ").Append(reader.GetString(2)).AppendLine();
  }
}
string s  = sb.ToString();

However, you can probably do it in TSQL via:

declare @result varchar(max) = ''
select @result = @result + 'ColoumA: ' + CONVERT(ColoumA, varchar(10)) +
          ', ColoumB ' + CONVERT(ColoumB, varchar(10)) + ', ' + ColoumC + '
' -- newline!
from ...

select @result

the select @result = @result + ... pattern does pretty much what you describe.

Upvotes: 3

Related Questions