如何在基于列的逻辑上合并 SQL 数据行? [英] How to merge rows of SQL data on column-based logic?

问题描述

我正在为我们的总帐编写保证金报告，我已经掌握了基本知识，但我需要根据特定逻辑合并行，但我不知道如何...

I'm writing a margin report on our General Ledger and I've got the basics working, but I need to merge the rows based on specific logic and I don't know how...

我的数据如下所示:

value1      value2      location  date          category                     debitamount    creditamount
2029        390         ACT       2012-07-29    COSTS - Widgets and Gadgets  0.000          3.385
3029        390         ACT       2012-07-24    SALES - Widgets and Gadgets  1.170          0.000

我的报告需要像这样一起显示两列:

And my report needs to display the two columns together like so:

plant   date          category               debitamount    creditamount
ACT     2012-07-29    Widgets and Gadgets    1.170          3.385

连接它们的逻辑包含在 value1 和 value 2 列中.如果值 1 的最后 3 位数字和值 2 的所有三位数字相同，则应合并行.此外，值 1 的第 1 位数字始终是销售额 2 和成本 3(不确定是否重要)

The logic to join them is contained in the value1 and value 2 column. Where the last 3 digits of value 1 and all three digits of value 2 are the same, the rows should be combined. Also, the 1st digit of value 1 will always been 2 for sales and 3 for costs (not sure if that matters)

IE 2029-390 是用于向客户出售小部件和小工具的收入，而 3029-390 是用于从供应商处购买小部件和小工具的资金.

IE 2029-390 is money coming in for Widgets and Gadgets sold to customers, while 3029-390 is money being spent to buy the Widgets and Gadgets from suppliers.

如何在我的存储过程中以编程方式执行此操作?(SQL Server 2008 R2)

How can I so this programmatically in my stored procedure? (SQL Server 2008 R2)

我是否会将 3000 加载到一个变量表中，将 2000 加载到另一个变量表中，然后在 value2 和 right(value1, 3) 上加入这两个表?或者类似的东西?

Would I load the 3000's into one variable table the and the 2000's into another, then join the two on value2 and right(value1, 3)? Or something like that?

推荐答案

试试这个:

SELECT RIGHT(LTRIM(RTRIM(value1)),3), value2, MAX(location),MAX(日期)、MAX(类别)、SUM(借方)、SUM(贷方) FROMtable1 GROUP BY RIGHT(LTRIM(RTRIM(value1)),3), value2

SELECT RIGHT(LTRIM(RTRIM(value1)),3) , value2, MAX(location), MAX(date), MAX(category), SUM(debitamount), SUM(creditamount) FROM table1 GROUP BY RIGHT(LTRIM(RTRIM(value1)),3), value2

它会将贷方金额和借方金额相加.它将选择其他列中的最大字符串值，假设当 value2 和 value1 的最后 3 位数字相同时它们始终相同，这无关紧要.

It will sum the credit amount and debit amount. It will choose the maximum string value in the other columns, assuming they are always the same when value2 and the last 3 digits of value1 are the same it shouldn't matter.

这篇关于如何在基于列的逻辑上合并 SQL 数据行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！