將 2 行合併為一行

| Year | Category | Number_of_products |
| 19   |Fruits1   | 25                 |
| 19   |Fruits2   | 45                 |
| 20   |Fruits1   | 50                 |
| 20   |Fruits2   | 50                 |

你知道如何轉換這張桌子，所以它會這樣嗎？將不勝感激任何幫助

| Year | Category | Number_of_products |
|------|----------|--------------------|
|19    |Fruits    | 70                 |
|20    |Fruits    | 100                |

我認為它適用於 SQL Server。如果您使用 MySql，您可以更改語法以與您的系統兼容：

with cte as
       (select 
       year,
       case when Category='Fruits1' then 'Fruits'
           when Category='Fruits2' then 'del'
           else Category end as Category,
       case when Category='Fruits1' then Number_of_products + (select Number_of_products from table1 as t2 where t2.year=t1.year and t2.Category='Fruits2' )
            when Category='Fruits2' then -1
            else Number_of_products end as value
       from table1  as t1)
   select * 
   from cte
   where value>0

您可以使用：

select `Year`,
      LEFT(Category,6) as new_Category,
      sum(Number_of_products) as tot_Number_of_products
from test_tbl
where LEFT(Category,6) ='Fruits' ---this could be written as  " Category like 'Fruits%' "
group by `Year`,new_Category
order by `Year` asc ;

https://dbfiddle.uk/3pZ2JRve

我推薦什麼。

1. 不要使用關鍵字和保留字，例如YEAR，如果可能，更好的做法是重命名。
2. 在 Category 列中，您有 Fruits1,Fruits2 之類的值，這似乎是多餘的，您可以將它們分組到一個主要類別中，例如 Fruits。

引用自：https://dba.stackexchange.com/questions/316915