asked on

Add preceding wording to SQL results.

Hi Experts,
I have the following formula, and would like to precede it with some wording (for example 'Here is your list'), in case the formula returns something, how can I do it?

SELECT snv_id = STUFF((
SELECT ', ' + snv_id + ' ' + CONVERT(VARCHAR(10), Visit_Date, 101) + ' ' + Client_Last_Name + ' ' + ' ' + Client_First_name + char(10) 
FROM _v_v_VisitNotes_Browser
where _v_v_VisitNotes_Browser.nurse_User_ID_num_SNV= '[@authfield:Nurse_UserName]'
FOR XML PATH('')
), 1, 1, '')

Open in new window

Thanks

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Hi,

And what is if I want add Char(10) after the wording?

What is the rule of string concatenation?

One more Q (if you dont mind-:)

The current list is giving me

 SNV103409 11/26/2019 A.  B.
, SNV103411 11/27/2019 A.  B.
, SNV103418 11/29/2019 A.  B

Open in new window

How can I change that to

 SNV103409 11/26/2019 A.  B.,
SNV103411 11/27/2019 A.  B.,
SNV103418 11/29/2019 A.  B

Open in new window

but for one record, no comma?

Thanks,
Ben

slightwv (䄆 Netminder)

>>How can I change that to

Append the comma AFTER the value?

>>but for one record, no comma?

A case statement:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6dc6daa4-7366-4f8d-8c61-0f3406b00b87/remove-last-comma-from-the-string?forum=transactsql

CASE 
	WHEN RIGHT(RTRIM(f1),1) = ',' THEN LEFT(f1,LEN(f1)-1)
	ELSE f1
	END AS f1

Open in new window

bfuchs

ASKER

Thanks