存在NULL varchar值时聚合数值(Aggregate numeric values while there is NULL varchar values)
我想在SQL Server 2012中将表A转换为表B.
相同的ID为Characters列返回两个不同的值; 一个是NULL,另一个是正确的值。 如何从“字符”列中删除NULL值并将其他数值相加到一行?
表A :
ID Amount Character --------------------------- 1001 2000 NULL 1001 500 Delight表B :
ID Amount Character ----------------------------- 1001 2500 Delight谢谢你的帮助
I want convert table A to table B in SQL Server 2012.
Same ID returns two different values for Characters columns; one is NULL and other is right value. How can I drop NULL value from the Characters column and sum other numeric values in a single row?
Table A:
ID Amount Character --------------------------- 1001 2000 NULL 1001 500 DelightTable B:
ID Amount Character ----------------------------- 1001 2500 DelightThanks for your help
最满意答案
使用Max作为[Character]:
Create table #TableA ([id] int, [Amount] int, [Character] nvarchar(20)) Insert into #TableA Values(1001, 2000, NULL) Insert into #TableA Values(1001, 500 , 'Delight') select [ID], Sum([Amount]), Max([Character]) from #TableA Group by [ID]Use Max for [Character]:
Create table #TableA ([id] int, [Amount] int, [Character] nvarchar(20)) Insert into #TableA Values(1001, 2000, NULL) Insert into #TableA Values(1001, 500 , 'Delight') select [ID], Sum([Amount]), Max([Character]) from #TableA Group by [ID]更多推荐
发布评论