Only two objects + rest the count

Question

I want to show the below string:

Apple,Banana,Cashew,Doughnut,Elephant,Fish

into this format:

Apple, Banana + 4 others

I only want to show first two objects and rest the count !

Answer 1

This, like others have said, isn't a job to SQL Server, but your presentation layer. Based on your expected results, I assume that your commas mean you are storing delimited data in your database; a fatal flaw. If you normalised your design, you could likely easily achieve this in your application layer.

As you are using denormalised data, then you need to first normalise it, then reaggregate it too. I use an arbitrary TOP (the rows could be different every time you run the query), as the ordinal parameter is only (currently) available in Azure SQL Database; hopeful the ordinal parameter will be in SQL Server 2022.

Anyway, this works, but again, fix your design, and do this in the application layer. If you aren't on SQL Server 2017, then that is a must, not a very strong suggestion.

DECLARE @Values int = 2; --parameterised, but you could hard code

WITH Split AS(
    SELECT SS.[value],
           COUNT(*) OVER () AS [Rows]
    FROM (VALUES('Apple,Banana,Cashew,Doughnut,Elephant,Fish'))V(YourDenormalisedData)
         CROSS APPLY STRING_SPLIT(V.YourDenormalisedData,',') SS),
ArbitraryTop AS(
    SELECT TOP (@Values)
           value,
           [Rows]
    FROM Split)
SELECT STRING_AGG(value,', ') +
       CASE WHEN MAX([Rows]) > @Values THEN CONCAT(' + ',MAX([Rows])-@Values,' others') ELSE '' END
FROM ArbitraryTop;

Answer 2

This, like others have said, isn't a job to SQL Server, but your presentation layer

If you must do it in sql, then maybe you can do it like this.
I do assume you have these values in multiple rows and need to concatenate them by some grouping, if not then make that more clear in your question

select o.id,
       ( select top 2 string_agg(o2.name, ', ')
         from   ( select top 2 o2.name, 
                         o2.id 
                  from   object o2 
                  where  o2.id = o.id
                ) o2
         where  o2.id = o.id
       ) + ' and ' + convert(varchar(50), count(o.name) - 2) + ' others'
from   object o
group by o.id

Look at this DBFiddle here to see it working

The result looks like this

id	(No column name)
1	Apple, Banana and 4 others
2	Peer, Catfish and 0 others
3	Dog, Whale and 1 others

If you don't want to show the 'and x others' you can alter the query like this

select o.id,
       ( select top 2 string_agg(o2.name, ', ')
         from   ( select top 2 o2.name, o2.id 
                  from   object o2 
                  where  o2.id = o.id
                ) o2
         where  o2.id = o.id
       ) + case when count(o.name) > 2 then ' and ' + convert(varchar(50), count(o.name) - 2) + ' others'
                else ''
           end     
from   object o
group by o.id

id	(No column name)
1	Apple, Banana and 4 others
2	Peer, Catfish
3	Dog, Whale and 1 others

EDIT for sql server that does not support string_agg

In case you have an older version of sql server that does not supports the string_agg function, you can do it with XML PATH to concatinate the values and stuff to remove the extra ,

select o.id,
       stuff(( select top 2 ', ' + o2.name
               from   ( select top 2 o2.name, 
                               o2.id 
                        from   object o2 
                        where  o2.id = o.id
                      ) o2
               where  o2.id = o.id
               for XML PATH('')
            ), 1, 2, ''  
       ) + case when count(o.name) > 2 then ' and ' + convert(varchar(50), count(o.name) - 2) + ' others'
                else ''
           end     
from   object o
group by o.id

DBFiddle using XML Path

The outcome will be the same again

Answer 3

I have created this, and it works for me. However, it needs optimising.

Declare @test_count int = 0, @test1_pos int = 0, @test2_pos int = 0, @test_product varchar(500), @test_product2 varchar(500),@tests varchar(500); 
--select distinct top 1 product,CHARINDEX(',',product,1),  CHARINDEX(',',product,CHARINDEX(',',product,1)+1) from ReportDB..wo_result_detail  where CustomerId=@LAB_CODE 

select distinct top 1 @test_product = product,@test1_pos=CHARINDEX(',',product,1), @test1_pos = CHARINDEX(',',product,CHARINDEX(',',product,1)+1)
from [table name]  where [condition] 

select distinct top 1 @test_product = product, @test_product2 =
case when @test1_pos <> 0 or  @test2_pos <> 0  then ( 
                select top 1 left(product,CHARINDEX(',',product,CHARINDEX(',',product,1)+1)-1) from [table name]  where [condition] 
                )
                else @test_product
                end from [table name]  where [condition] 

select @test_count = (len(replace(@test_product,',',', '))+1) - len(@test_product)

select top 1 @tests= case when @test_count> 2 then  concat ( @test_product2 ,' + ',@test_count-2 ,' Others') 
else @test_product end

Answer 4

This task can be solved with the power of the cursor, by dividing the line by the delimenter using function split_string.

  --@string - our input string
DECLARE @string NVARCHAR(MAX) = 'Apple,Banana,Cashew,Doughnut,Elephant,Fish';

--@count - the number of words in @string
DECLARE @count INT = 0;

--@countrestwords - count of rest words
DECLARE @countrestwords INT = 0;

--@resultstring - result string
DECLARE @resultstring NVARCHAR(MAX) = '';

        DECLARE stringcursor CURSOR FOR 
        SELECT 
            VALUE 
        FROM string_split(@string,',')

        OPEN stringcursor 

        FETCH FROM stringcursor INTO @string
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @count = 0
                    BEGIN
                        SET @resultstring = @string;

                    END
                ELSE IF @count = 1
                    BEGIN
                        SET @resultstring = @resultstring +',' +@string ;
            
                    END
                ELSE 
                    BEGIN
                        SET @resultstring = @resultstring;
                        SET @countrestwords = @countrestwords + 1;

                        --SELECT @countrestwords
                    END


                SET @count = @count + 1;


                FETCH NEXT FROM stringcursor INTO @string

            END

        CLOSE stringcursor
        DEALLOCATE stringcursor

SELECT @resultstring + ' + ' + CONVERT(NVARCHAR(MAX),@countrestwords)+' others' ;

GO