Only two objects rest the count-CodePudding

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 !

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

That's it, 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;

--@count_restwords - fix the last iteration
DECLARE @count_restwords INT = 0;

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

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

        OPEN string_cursor 

        FETCH FROM string_cursor INTO @string
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @count = 0
                    BEGIN
                        SET @result_string = @string;

                    END
                ELSE IF @count = 1
                    BEGIN
                        SET @result_string = @result_string  ','  @string ;
            
                    END
                ELSE 
                    BEGIN
                        SET @result_string = @result_string;
                        SET @count_restwords = @count;
                    END


                SET @count = @count   1;


                FETCH NEXT FROM string_cursor INTO @string

            END

        CLOSE string_cursor
        DEALLOCATE string_cursor

SELECT @result_string   ' '   CONVERT(NVARCHAR(MAX),@count_restwords) ;

GO

Please check from your side and let me know. Best regards.