在sql中基于单个group by聚合多个列

Question

我有一个表，其中包含附加图像格式的数据

​

或样本数据

transactionDate         |   AllocatedHrs | ActivityName                     
2014-10-10 10:43:55.763,1.5830000000000000,abc
2014-10-10 10:13:55.763,1.2830000000000000,def
2014-10-10 11:33:55.763,0.2830000000000000,ghi
2014-10-10 11:53:55.763,0.7170000000000000,ijk

我需要根据transactionDate对这个表的数据进行分组，应该添加AllocatedHrs的数据，第三列应该添加分组的表的名称，即

network                        | Sum                    |Date
abcdef2014-10-10 10:43:55.763, 2.8660000000000000, 2014-10-10 10:43:55.763
ghiijk2014-10-10 11:33:55.763,   1.0,              2014-10-10 11:33:55.763

我使用以下查询

with A as 
(
  SELECT DATEADD(hour, DATEDIFF(hour, 0, transactionDate), 0) as testDate, 
   activityId , AllocatedHrs ,activity_name
  from test_group 
  group by activityId ,transactionDate,AllocatedHrs,activity_name
  )
  select  cast(a.activity_name as VARCHAR) +  cast(a.testDate as VARCHAR) as network
  ,sum(A.AllocatedHrs)as allc 
  ,a.activityId as activity
  from A
  group by a.testDate
  order by a.activityId

但是，每当我尝试运行相同的命令时，都会得到以下错误

Column 'A.activity_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

DECLARE @table1 TABLE
  (
     transactionDate DATETIME,
     AllocatedHrs    NUMERIC(22, 10),
     ActivityName    VARCHAR(200)
  )

INSERT INTO @table1
VALUES      ('2014-10-10 10:00:00.000',1.5830000000000000,'abc'),
            ('2014-10-10 10:00:00.000',1.2830000000000000,'def'),
            ('2014-10-10 11:00:00.000',0.2830000000000000,'ghi'),
            ('2014-10-10 11:00:00.000',0.7170000000000000,'ijk')

SELECT t1.transactionDate,
       Sum(AllocatedHrs),
       (SELECT CONVERT(VARCHAR, ActivityName)
        FROM   @table1 b
        WHERE  b.transactionDate = t1.transactionDate
        ORDER  BY ActivityName
        FOR XML PATH(''))
       + CONVERT(VARCHAR, t1.transactionDate, 111 )
FROM   @table1 t1
GROUP  BY t1.transactionDate