How to combine text from multiple lines into one text line?

Question

SQL Server

Table

AZone2Adapter].[dbo].[_data_OrganizationCustomerSpecialties] ( [Id], [OrganizationCustomerId], [SpecialtyId])

How to display all columns with a condition in a script.?

DECLARE @TextProduct NVARCHAR(MAX);
SELECT @TextProduct = ISNULL(@TextProduct + ', ','') + QUOTENAME([OrganizationCustomerId])
  FROM [AZone2Adapter].[dbo].[_data_OrganizationCustomerSpecialties]
  WHERE [OrganizationCustomerId]='1002775640209670'
SELECT @TextProduct AS TextProduct

If you write:

SELECT @TextProduct = ISNULL(@TextProduct + ', ','') + QUOTENAME([OrganizationCustomerId]),[Id]

that will be an error.
STRING_AGGdoes not work, only for SQL Server 2017

Answer 1

CONCAT

DECLARE @TextProduct NVARCHAR(MAX);
SELECT @TextProduct = CONCAT_WS( ', ', @TextProduct, [OrganizationCustomerId])
  FROM [AZone2Adapter].[dbo].[_data_OrganizationCustomerSpecialties]
  WHERE [OrganizationCustomerId]='1002775640209670'
SELECT @TextProduct AS TextProduct

Microsoft SQL Server 2008

DECLARE @TextProduct NVARCHAR(MAX);
SELECT @TextProduct = STUFF(COALESCE(', ' + @TextProduct, '') 
                          + COALESCE(', ' + [OrganizationCustomerId], ''), 1, 2, '')
  FROM [AZone2Adapter].[dbo].[_data_OrganizationCustomerSpecialties]
  WHERE [OrganizationCustomerId]='1002775640209670'
SELECT @TextProduct AS TextProduct