Adding 'and' at the end of a comma separated list

Question

Currently, I'm using the stuff function to create a comma separated list per each row.

x,y,z

What I want is to add commas for n-1 items in the list, with the final item being preceded by 'and'

x,y, and z.

For these purposes, just checking row number won't work because this list is being generated per unique Id, therefore I can't just iterate to the end of the table. Code below:

SELECT DISTINCT (sw.OwnerID)
            ,stuff((
                SELECT DISTINCT ', ' + e.pn
                FROM fct.enrtablev e
                    WHERE sw.OwnerID = e.OwnerId
                    FOR XML PATH('')), 1, 1, '') AS [Pet(s)]

Answer 1

A bit of a hack... AND string_agg() would be a better fit if 2017+

Here we use test the row_number() of the item count sum(1) over(), when equal this is the last item in the list

Example

Declare @YourTable table (OwnerID int,pn varchar(50))
Insert Into @YourTable values
 (1,'X')
,(1,'Y')
,(1,'Z')
,(1,'Z')
,(2,'Apples')

Select Distinct
      OwnerID
     ,stuff( ( Select case when row_number() over(order by pn) = nullif(sum(1) over() ,1)
                           then ', and ' 
                           else ', ' 
                       end + pn  
                FROM (Select distinct pn 
                       From @YourTable
                        Where OwnerID = A.OwnerId
                     ) e 
                Order By PN
                For XML Path('')), 1, 2, '')  AS [Pet(s)]
 From @YourTable A

Returns

OwnerID Pet(s)
1       X, Y, and Z
2       Apples