sql-server - 合并一列，同时对具有相似值的另一列进行分组

Question

我一直在研究 SQL Server 存储过程，以找到下面结果表中所示的解决方案。我的解决方案很接近，但是我在一个我想完全分组的列上得到了多个结果。

DECLARE @fiscal_year AS INT
SET @fiscal_year = '2016'

SELECT
    [ActionDate] AS [PPDate],
    SUM([GAL].[GCount]) AS [GCount],
    SUM([GAL].[LCount]) AS [LCount],
    CONCAT(SUM([GAL].[GCount]), ' Gain(s): (', [GAL].[GComments], '); ', SUM([GAL]. 
    [LCount]), ' Loss(es): (',  [GAL].[LComments], ')') AS [Details]
FROM
    [dbo].[TableA] [GAL]
GROUP BY
    [GAL].[ActionDate], [GAL].[GainComments], [Gal].[LossComments]

我有一张如下表。

表 A：

ID  ActionDate  GCount  GainRID GComments   LCount  LossRID LComments
1   2013-06-04  1       35      John        0       -1      NULL
2   2013-06-04  2       35      M & R       0       -1      NULL
3   2014-01-10  1       60      Paul        0       -1      NULL
4   2014-01-10  1       60      Mona        0       -1      NULL
5   2013-10-05  3       58      Tim         0       -1      NULL
6   2013-10-05  2       58      Ruby        0       -1      NULL
7   2013-10-05  0       -1      NULL        2       50      Jude & Mo
8   2013-10-05  0       -1      NULL        1       50      Frank
9   2013-10-05  0       -1      NULL        1       50      Linda
10  2014-01-10  0       -1      NULL        1       70      Eliz
11  2014-01-10  0       -1      NULL        1       70      Georgina
12  2014-01-10  0       -1      NULL        1       70      Christina
13  2013-10-05  0       -1      NULL        2       64      Joan & Mike
14  2013-10-05  0       -1      NULL        1       64      Mitch 

...并且我希望使用 GainCount 和 LossCount 字段上的 SUM() 函数将其按操作日期分组。Details 字段由 GainReasonID、LossReasonID、Gain 和 Loss Comments 组合而成，因此表格最终如下所示。

Results
PPDate      GCount  LCount  Details
2013-06-04  2       0       "(2) Gains: 2(35) -- John, M & R 
                             (0) Losses: "
2013-10-05  2       3       "(2) Gains: 2(58) -- Tim, Ruby
                             (3) Losses: 3(50) -- Jude & Mo, Frank, Linda , 
                                 2(64) -- Joan & Mike, Mitch"
2014-01-10  2       3       "(2) Gains: 2(60) -- Paul, Mona
                             (3) Losses: 3(70) -- Eliz, Georgina, Christina"

标签： sql-serverconcatenationrdbms