T-SQL: How to concatenate a comma to a text field for all but final record

Question

I would like to know how to write query that can append a comma to a text-field for all records selected except for the final record.

I want to create a generic function which can wrap a query to create enumeration assignment statements for C# and TypeScript. Here's a generic query:

SELECT ATextField, AnIntegerField
FROM table1 join table2 .... join tableN

The wrapper function should return distinct values sorted by the text field in the following format:

TextValue1 = 15,
TextValue2 = 3,
...
TextValueN = 128 --No comma here on the final record

Can you add the sample data of `ATextField` and `AnIntegerField` — Pரதீப், May 21 '16 at 13:27
You might have another reason, but the only reason I can think of to do this is to eventually put this into a single, comma-delimited field. If that's eventually what you're doing, look [here](http://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into). — Jeremy Fortune, May 21 '16 at 13:58
Why are you trying to do this in TSQL? If you are reading this in C# then do it there (and it is a simple task). — paparazzo, May 21 '16 at 15:13

score 1 · Answer 1 · answered May 21 '16 at 13:44

Didn't try it as you didn't not provide any sample data, but probably you could use the LEAD function to check whether there is any following row, if not you set a "comma":

https://msdn.microsoft.com/en-us/library/hh213125.aspx

SELECT ATextField + '=' + cast(AnIntegerField as varchar(50)) + 
case when lead(xxxxxxx add specific code here xxxxxx) then ',' else '' end
FROM table1 join table2 .... join tableN

score 0 · Accepted Answer · answered May 23 '16 at 17:15

Following up on Reboon's suggestion, here's a pluggable solution:

WITH cte (KeyNumber, TextValue) AS (
    SELECT t1.AnIntegerField, t3.ATextField
    FROM Schema1.Table1 t1
    JOIN Schema2.Table2 t2 ON t1.JField1 = t2.JField2
    JOIN Schema3.Table3 t3 ON t2.JField1 = t3.JField2
    WHERE t1.FField1 = N'filter on this value'
)
SELECT TextValue + N' = ' + CONVERT(NVARCHAR(10), KeyNumber) + CASE WHEN (LEAD(1) OVER(ORDER BY TextValue)) IS NULL THEN N'' ELSE N',' END AS EnumAssignment
FROM (
SELECT DISTINCT TOP 100 PERCENT KeyNumber, TextValue
FROM cte
ORDER BY TextValue ) w

T-SQL: How to concatenate a comma to a text field for all but final record

2 Answers2