SQL Server 2 commas & 1 and

Question

I'm making a view and in one column I will have some names concatenated together.

Ideally in this one column I want the result to look something like the below:

ResultA, ResultB, ResultC and ResultD  
ResultB, ResultC and ResultD  
ResultA and ResultD

So I want it to keep putting commas between results until it reaches the last result where it places an AND in front of it instead of a comma.

I can do the query to grab what columns I need but can't figure out how to do the above...

select 
    isnull(replace(isnull(givenname, '') + ' ' +
       (isnull(middlename, '') + ' ' + (isnull(lastname, ''), ' ', ' '), '') + 
       isnull(companyname, '') as Name
from
    table1 with (nolock) 
left outer join
    table2 on t1key = t2key     
left outer join
    table3 ON t1key = t3key

I found this query somewhere else that will put everything with a comma for me, but I still need the 'and' for before the last result.

SELECT 
    ID, Name,
    STUFF((SELECT ',' + CAST(T2.SomeColumn AS VARCHAR(MAX))
           FROM @T T2 
           WHERE T1.id = T2.id AND T1.name = T2.name
           FOR XML PATH('')), 1, 1, '') SOMECOLUMN
FROM 
    @T T1
GROUP BY 
    id, Name

sticky bit · Answer 1 · 2018-08-10T01:48:32.987

You can use the row_number() window function to enumerate the rows. When the number is equal to 1, take that as the last row and prepend ' and ' else prepend ', ' -- use a CASE ... END for that.

But make sure you also order the whole result by the same expression you used in the OVER clause for row_number() but in the opposite direction (ASC instead of DESC an vice versa). Otherwise you had the ' and ' before the first value instead the last one.

Since the prepended strings no longer have a uniform length (', ' vs ' and ') the usual stuff() won't easily work. One solution for that problem is to also compare the row number against the number of rows the table has -- we can get that in a subquery. If they're equal skip the prepending. Again a CASE ... END can be used, the other gets nested in it.

As your schema isn't clear to me, I give an unrelated example:

CREATE TABLE elbat
             (nmuloc varchar(8));

INSERT INTO elbat
            (nmuloc)
            VALUES ('x'),
                   ('y'),
                   ('z');

SELECT coalesce(CASE
                  WHEN row_number() OVER (ORDER BY nmuloc DESC) <> (SELECT count(*)
                                                                           FROM elbat) THEN
                    CASE row_number() OVER (ORDER BY nmuloc DESC)
                      WHEN 1 THEN
                        ' and '
                      ELSE
                        ', '
                    END
                END,
                '') + nmuloc
       FROM elbat
       ORDER BY row_number() OVER (ORDER BY nmuloc ASC)
       FOR XML PATH('');

db<>fiddle

SQL Server 2 commas & 1 and

1 Answers1