如何合并包含相同两个“键”值的行的行？列？ [英] How can I consolidate rows that contain values for the same two "key" columns?

问题描述

我的存储过程在很大程度上是返回我想要的数据。但是，在一些孤立的情况下，它返回两个记录，其中应该只有一个。具体来说，在坏情况下，有两个具有相同Description和MemberName的记录。每个Description和MemberName组合应该只有一行（max）。而不是单独列出它们，两个记录的值应合并为一个。



IOW，数据应如下：

My Stored Procedure is, for the most part, returning the data I want. However, in a few isolated cases, it returns two records where there should just be one. Specifically, in the "bad" cases, there are two records with the same Description and MemberName. There should only be one row (max) for each Description and MemberName combination. Rather than list them seperately, the values of the two records should be combined into one.

IOW, the data should be like this:

PLATYPUSDESCRIPTION	CRITTERNAME	WEEK1VAL	WEEK2VAL
-----------------	-------------	--------	--------
Platypus		Bill		7		42
Duckbill		Pat		40		76

...但它包含一些重复的PLATYPUSDESCRIPTION + CRITTERNAME值，如下所示：

...but it contains some duplicated PLATYPUSDESCRIPTION + CRITTERNAME values like so:

PLATYPUSDESCRIPTION	CRITTERNAME	WEEK1VAL	WEEK2VAL
-----------------	-------------	--------	--------
Platypus		Bill		7		42
Duckbill		Pat		40		76
Duckbill		Pat		40		99

在上面的例子中，第2行应该是：

In the case above, row 2 should be:

Duckbill		Pat		80		175

...而且应该没有第3行。



这是SP的最后一部分，汇集了返回数据：

...and there should be no row 3.

Here is the last part of the SP, where the assembled data is returned:

SELECT PLATYPUSDESCRIPTION, CRITTERNAME, TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

为了避免PLATYPUSDESCRIPTION + CRITTERNAME重复，我尝试在GROUP BY中连接PLATYPUSDESCRIPTION和CRITTERNAME：

In order to avoid PLATYPUSDESCRIPTION + CRITTERNAME duplicates, I tried concatenating PLATYPUSDESCRIPTION and CRITTERNAME in a GROUP BY:

SELECT PLATYPUSDESCRIPTION, CRITTERNAME, TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
GROUP BY PLATYPUSDESCRIPTION + CRITTERNAME
ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

...并在GROUP BY中单独列出PLATYPUSDESCRIPTION和CRITTERNAME：

...and listing PLATYPUSDESCRIPTION and CRITTERNAME singly in a GROUP BY:

SELECT PLATYPUSDESCRIPTION, CRITTERNAME, TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
GROUP BY PLATYPUSDESCRIPTION, CRITTERNAME
ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

...并尝试以这种方式在PLATYPUSDESCRIPTION和CRITTERNAME上使用DISTINCT：

...and tried to use DISTINCT on PLATYPUSDESCRIPTION and CRITTERNAME this way:

SELECT DISTINCT(PLATYPUSDESCRIPTION), DISTINCT(CRITTERNAME), TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
GROUP BY PLATYPUSDESCRIPTION, CRITTERNAME
ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

......就这样：

...and this way:

SELECT DISTINCT(PLATYPUSDESCRIPTION + CRITTERNAME), TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
GROUP BY PLATYPUSDESCRIPTION, CRITTERNAME
ORDER BY PLATYPUSDESCRIPTION, CRITTERNAME;

...但是他们都不会编译。



如何合并描述相同PLATYPUSDESCRIPTION + CRITTERNAME的行？

...but none of them would even compile.

How can I consolidate rows that describe the same PLATYPUSDESCRIPTION + CRITTERNAME?

推荐答案

根据问题中给出的输入数据和预期结果，您可以使用SUM和GROUP BY来获取您要查找的输出。



我根据列创建了一个表，并将问题中提供的三行插入到表中。

From your input data and expected result given in the question, you can use SUM and GROUP BY to get the output you are looking for.

I created a table based on the columns and inserted three rows you provided in the question into the table.

PLATYPUSDESCRIPTION    CRITTERNAME    WEEK1VAL    WEEK2VAL
-------------------    -----------    --------    --------
Platypus		       Bill	          7		      42
Duckbill		       Pat		      40		  76
Duckbill		       Pat		      40		  99

此查询将产生您的预期结果：

This query will produce your expected result:

select PLATYPUSDESCRIPTION, CRITTERNAME, sum(WEEK1VAL) as WEEK1VAL, sum(WEEK2VAL) as WEEK2VAL from 
dbo.TestTable group by PLATYPUSDESCRIPTION,CRITTERNAME

输出这个查询是：

The output of this query is:

PLATYPUSDESCRIPTION    CRITTERNAME    WEEK1VAL    WEEK2VAL
-------------------    -----------    --------    --------
Platypus	           Bill	          7	          42
Duckbill	           Pat	          80	      175

希望这有助于指出正确的方向。

Hope this helps to point you in the right direction.

这篇关于如何合并包含相同两个“键”值的行的行？列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！