There is a table (group_id int, text varchar (max), sum decimal (19,2))

data

1 | qwe | 3.00 1 | asd | 5.00 2 | zxc | 10.00 2 | 123 | 15.00

You need an indexed view that will return

 1 | qwe,asd | 8.00 2 | zxc,123 | 25.00

    3 answers 3

    In this case, you can create a regular view, indexed - no.

    Any way that you can perform a concatenation in SqlServer will fall under the restrictions (for a link , see the Additional Requirements section).

    The fact is that if aggregation is used in the indexed view, it should not only be able to aggregate the data when adding or changing rows in the tables on which it is based, but also de-aggregate them when deleting rows. And in order to improve performance, this is done incrementally, rather than completely rebuilding the index.

    With functions like sum() and count() this can be done easily and unambiguously. If rows are added to the table, their [sum] values ​​will be added to the corresponding rows of the indexed view. If lines are deleted, their [sum] deducted.

    With concatenation, you can't do this. Suppose we have the strings asd , qwe and d,q in the same group. By concatenating them separated by commas, we get in the indexed view the string asd,qwe,d,q .

    Now let's say a line is deleted with d,q . How to determine where in asd,qwe,d,q need to remove d,q ? Definitely - no way. Therefore, such things are prohibited.

    The alternative is an additional permanent table that will be updated (for example, by a trigger) when the data in the main table changes.

       SELECT group_id, GROUP_CONCAT(text SEPARATOR ','), sum(``sum``) FROM table GROUP BY group_id;
      • What is analog for T-sql function Group_Concat ()? - Ruslan_K
      • Yes, I apologize, there is no such function in the sql-server, but you can achieve the same behavior using the stuff social.msdn.microsoft.com/Forums/sqlserver/en-US/… function - Vladimir Pavluk
      • Found if anyone is interested in GROUP_CONCAT for SQL Server - Ruslan_K
      • @VladimirPavluk, this is not what I need. For each individual column will have to request the same data. Before retirement, I will wait for the execution of this request ... besides, they still say that I cannot create an indexed view - iRumba
      • iRumba I do not understand why this is not what you need. Why For each separate column you will have to request the same data - explain what you mean here, I just do not understand the information hidden behind these words. Is SELECT group_id, sum ( sum ) FROM table GROUP BY group_id; Does this treatment take significant processing time? explain your answer - Mcile
       select group_id ,left( ( select [text] + ',' from table t2 where t2.group_id = t1.group_id for xml path('') ) ,len ( ( select [text] + ',' from table t2 where t2.group_id = t1.group_id for xml path('') ) ) - 1 ) as s123 , sum([sum]) as [sum] from table t1 group by group_id