Formatting output of a SQL query

Question

I have a query result of two columns that looks like this

name      text
---------------
John      hello
Carl      hi
Doe       my text
Alice     another text

I would like to find a way how to format this result as a text. What I need is this output:

John:
hello
_____
Carl:
hi
_____
Doe:
my text
_____
Alice:
another text
_____

So in other words I need to take the first column's value as a text, append some text to it, append second column's value as a text and append some more text. And do that for every row.

Is there an easy way of how to do this using only SQL Server's syntax?

EDIT: I am only using Microsoft SQL Server Management Studio and I need the whole result as a plain text. I was thinking of writing a function that would return it?

EDIT2: I am using SQL Server 2016

It's not completely clear what you're expecting as a resultset; is that a single row and single column? This looks like a job for your application code tbh. — Stu
– Stu, Commented Sep 26, 2021 at 12:19
As per requirement two column value of a row will show two rows and one column. Right? if so then please check this url stackoverflow.com/questions/69328675/… — Rahul Biswas
– Rahul Biswas, Commented Sep 26, 2021 at 12:22
I edited my question to clarify that I need just one text value for the whole result. — P. Paul
– P. Paul, Commented Sep 26, 2021 at 12:29
The fact that they always did return in that order is not an indication that they always do so in the future, you need an order by clause — Charlieface
– Charlieface, Commented Sep 26, 2021 at 13:13

Charlieface · Accepted Answer · 2021-09-26 12:48:08Z

1

Simple string aggregation should do the trick

SELECT
  STRING_AGG(CAST(CONCAT(
    name,
    ':
',
    text,
    '
_____'  
  ) AS nvarchar(max)), '
')

FROM YourTable

Or for SQL Server 2016 or earlier, you can use FOR XML aggregation

SELECT STUFF((
  SELECT
    CONCAT('
',
      name,
      ':
',
      text,
      '
_____')
  FROM YourTable
  FOR XML PATH, TYPE
).value('text()[1]','nvarchar(max)'), 1, 1, '')

edited Sep 26, 2021 at 12:48

answered Sep 26, 2021 at 12:31

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

P. Paul Over a year ago

Unfortunately my SQL Server version (2016) does not seem to support this. Do you know of any alternative?

Charlieface Over a year ago

OK done that now

Collectives™ on Stack Overflow

Formatting output of a SQL query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related