存在NULL varchar值时聚合数值(Aggregate numeric values while there is NULL varchar values)

存在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 Delight

Table B:

ID Amount Character ----------------------------- 1001 2500 Delight

Thanks 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]