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

user_15698135 0
Viewed 98

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)]
tsql sql-server sql
3 Answers

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
user_15700000

XQUery and XML data model is based on ordered sequences. Exactly what we need.

Here is a simple solution based on XQuery and its FLWOR expression.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (OwnerID int, pn VARCHAR(50));
INSERT INTO @tbl (OwnerID, pn) VALUES
(1,'X'),
(1,'Y'),
(1,'Z'),
(2,'Apples');
-- DDL and sample data population, end

SELECT p.OwnerID 
    , (SELECT * 
    FROM @tbl AS c
    WHERE c.OwnerID = p.OwnerID
    FOR XML PATH('r'), TYPE, ROOT('root')
    ).query('
    for $x in /root/r/pn/text()
    return if ($x is (/root/r[last()]/pn/text())[1]) then 
            if (count(/root/r) gt 1) then concat("and ", $x) else string($x)
         else concat($x, ",")
   ').value('.', 'VARCHAR(MAX)') AS Result
FROM @tbl AS p
GROUP BY p.OwnerID;

Output

+---------+----------------+
| OwnerID |     Result     |
+---------+----------------+
|       1 | X, Y, and Z    |
|       2 | Apples         |
+---------+----------------+
user_19323110

You can achieve this using ORDER BY and count.

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

;WITH CTE_OwnerIdRank as
(
SELECT ownerid, pn, row_number() over (order by ownerId) as totalrn,
count(*) over(partition by ownerid order by ownerid) as ownercnt
from @yourtable
)
SELECT distinct OwnerId,
            stuff((
                SELECT  ', ' + CASE WHEN c.totalrn = c.ownercnt then CONCAT(' and ',c.pn) else c.pn end
                FROM CTE_OwnerIdRank as c
                    WHERE c.OwnerID = o.OwnerId
                    order by c.totalrn
                    FOR XML PATH('')), 1, 1, '') AS [Pet(s)]
from @yourtable as o
OwnerId Pet(s)
1 X, Y, and Z
2 Apples
user_6349350
Related