0

Is there a way to create a text-based output from a select statement where it might look like: 

UID         Department  ProdCode    Product Description
----------  ----------  ----------  -------------------
4           DeptA       12345678    Product A
6           DeptB       87654321    Product B

I tried using: FOR XML. I can convert it to HTML that way, but I need a text version of this. There is FOR JSON also, so was hoping there was something like FOR TEXT. But I can't find anything :(

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Andrew
  • 437
  • 7
  • 18
  • 2
    Are you using Xslt to transform your FOr XML result to html? If so, you can easily transform it to plain text using Xslt. – Filburt Sep 20 '18 at 14:25
  • As you haven't mentioned file output or anything, I assume you have Resuts to Grid on - change it to Results to Text in SSMS and it will look how you want. If you do need output to text file, try BCP. – Dave Brown Sep 20 '18 at 14:25
  • 3
    This is something for your presentation layer... SQL-Server is absolutely the wrong tool for this. What is a "text table"? Is it padded to a fix width displayed with *Courier New*? Or is it with *tab* steps and aware of character width? What to do with word wrap if there are long strings? – Shnugo Sep 20 '18 at 14:26
  • I am basically taking this text and emailing it... so it will be run in a SP on a timed basis. As far as XSLT, no... I really don't even want to do XML at all, I just want it converted to a text table, stored in a varchar(max) so that I can send it out to look like a table (similar to the tabbed table in the post above) – Andrew Sep 20 '18 at 14:28
  • Again....what is a "text table". That clearly means something to you but nobody else knows what that is. Are you trying to make it like an HTML table? The only way to get properly formatted data like that is HTML or a fixed width font with padded values. Unless you can provide some details about what you want to accomplish I don't see this going anywhere. – Sean Lange Sep 20 '18 at 14:33
  • 2
    Andrew, this works, as long as you can rely on a fix width font on the reciever's side. An *html* table would work, as long as you can rely on an *html* viewer on the reciever's side. You can use `CONCAT()` and add all your parts padded to a fitting width. You can use an approach like [here](https://stackoverflow.com/a/39487565/5089204) to create an *html* table. And you can send your data as XML together with an XSLT to create an *html* table from the XML. – Shnugo Sep 20 '18 at 14:34
  • ok, makes sense. The receiver is an HTML viewer (email), but the problem is that the native application sends the email out as text only - so Outlook then formats as text even though it is HTML. I will probably have to use CONCAT, but better than nothing. – Andrew Sep 20 '18 at 14:39

1 Answers1

1

In SSMS in the Window with the query just right click and select results to text.

paparazzo
  • 44,497
  • 23
  • 105
  • 176