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

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

Answer 4

Here's what the MDN docs for Object.prototype have to say:

all objects inherit methods and properties from Object.prototype, although they may be overridden (except an Object with a null prototype, i.e. Object.create(null)).

In other words, Object.prototype is the root of almost all objects. Both Object and Function are children of Function.prototype, which is itself a child of Object.prototype.

I find inheritance in Javascript to be far easier to understand when I ignore the constructor functions, and instead focus on prototype chains. Doing this, for me, makes both the questions as well as the answers simpler; questions like:

• what is the prototype chain of an object?
• given two objects x and y, is x in y's prototype chain? (or: does y inherit from x?)

You can easily investigate prototype chains yourself with this little snippet (helpful docs here):

function getPrototypes(obj) {
    var protos = [],
        parent = obj;
    while ( true ) {
        parent = Object.getPrototypeOf(parent);
        if ( parent === null ) {
            break;
        }
        protos.push(parent);
    }
    return protos;
}

According to this function, primitives don't have prototypes:

> getPrototypes(3)
TypeError: Object.getPrototypeOf called on non-object

So let's leave primitives out of the picture. For objects, the hierarchy looks like this (where the children are indented to the right of their parent). As far as I know, true multiple inheritance is impossible in Javascript, and so every object has a single parent, with the exception of Object.prototype, which has no parent:

• Object.create(null)
• Object.prototype
  • arguments
  • Object.create(Object.prototype)
  • {}
    • Object.create({}) -- assuming {} is the object from the previous line, not a separate new object
  • JSON
  • Math
  • Array.prototype
    • []
    • new Array()
  • Function.prototype
    • Array
    • Object
    • Function
    • Number
    • Boolean
    • String
    • RegExp
    • function MyFunction() {}
    • Object.keys
    • Object.prototype.toString
    • Object.prototype.toString.call
    • getPrototypes
  • MyFunction.prototype
    • new MyFunction()
  • String.prototype
    • new String('abc')
      • Object.create(new String('abc'))
  • Number.prototype
    • new Number(41)
  • Boolean.prototype
    • new Boolean()
    • new Object(false)
  • RegExp.prototype
    • /a/

This is very confusing! Notice that for most cases, X.prototype is not a prototype of X! If we had some better terminology, the situation would probably be improved; however ... we don't :( As you can see, constructors such as Number and Boolean are in a separate sub-hieararchy from the objects they produce. Also, Object itself inherits from Function.prototype inherits from Object.prototype.

If you try these examples out, you will also find that using the constructor property is a poor way to inspect an object's prototypes, for a couple of reasons. Given var str1 = new String('abc'); var str2 = Object.create(str1);, here's why:

1. an object can be an instance of multiple constructors: str1 instanceof String and str1 instanceof Object are both true. This isn't reflected in the constructor property: str1.contructor === String

2. sometimes, we can't find a constructor function for each object in an object's prototype chain: Object.getPrototypeOf(str2).constructor === String. This is because the constructor property is inherited from String.prototype: both str1.hasOwnProperty('constructor') and str2.hasOwnProperty('constructor') are false, while Object.getPrototypeOf(str1).hasOwnProperty('constructor') is true. Fortunately, you can use the Object.prototype.isPrototypeOf method for this instead: str1.isPrototypeOf(str2) is true.

Answer 5

You're misunderstanding it.

Everything in Javascript (including all functions) is an object.

However, every object is an instance of a function. (as specified by the object's constructor property)