如何连接多行的一列(查询中使用的group子句)

Question

我在根据我的要求填充一些数据时遇到了一些问题，这如下所示。

表数据：

---------------------------------------------
EMPID  PROJ_TYPE  HOURS  ST_DATE    ED_DATE
---------------------------------------------
1      DEV        10     2013-1-1   2013-5-1
1      DEV        34     2013-5-2   2013-8-1
1      DEV        23     2013-8-2   2013-10-1
1      TEST       12     2014-1-1   2014-3-1
1      TEST       25     2014-3-2   2014-6-1

在这里，我想用小时来获取员工数据，所以我简单地写了

SELECT EMPID,
       PROJ_TYPE, 
       SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV, 
       SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
FROM TABLE1
GROUP BY EMPID, PROJ_TYPE

因此，它将按预期返回两行，但在这里，我希望每个员工只返回一行。如果employee有多个PROJ_TYPE，那么它应该组合两个项目值并只返回一行。

最后，我的结果集应该如下所示：

----------------------------
EMPID  PROJ     DEV  TEST
----------------------------
1     DEV/TEST  67   37

希望我很清楚，如果对我的问题有任何疑问，请发表意见。

Answer 1

支点将适用于这一点。

测试数据：

Declare @Test Table
(
 EMPID          Int
,PROJ_TYPE      NVarchar(4)
,HOURS          Int
,ST_DATE        Date
,ED_DATE        Date
);
Insert Into @Test
(EMPID, PROJ_TYPE, HOURS, ST_DATE, ED_DATE)
Values ('1', 'DEV','10','2013-1-1','2013-5-1')
        ,('1', 'DEV','34','2013-5-2','2013-8-1')
        ,('1', 'DEV','23','2013-8-2','2013-10-1')
        ,('1', 'TEST','12','2014-1-1','2014-3-1')
        ,('1', 'TEST','25','2014-3-2','2014-6-1')
        ,('2', 'DEV','7','2013-5-2','2013-8-1')
        ,('2', 'DEV','14','2013-8-2','2013-10-1')
        ,('2', 'TEST','12','2014-1-1','2014-3-1')
        ,('2', 'TEST','19','2014-3-2','2014-6-1')
        ,('3', 'DEV','34','2013-5-2','2013-8-1')
        ,('3', 'DEV','8','2013-8-2','2013-10-1')
        ,('3', 'TEST','12','2014-1-1','2014-3-1')
        ,('3', 'TEST','7','2014-3-2','2014-6-1')
        ,('3', 'DEV','23','2013-8-2','2013-10-1')
        ,('3', 'TEST','18','2014-1-1','2014-3-1')
        ,('4', 'TEST','25','2014-3-2','2014-6-1');

查询：

Select      EMPID
            ,IsNull(DEV,0) As DEV
            ,IsNull(TEST,0) As TEST
From        (
            Select      EMPID
                        ,PROJ_TYPE
                        ,HOURS
            From        @Test
            ) sq
    Pivot   (
            Sum(HOURS)
            For PROJ_TYPE In ([DEV],[TEST])
            ) piv;

SQL：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6d9a0ceb9dbf13e8af351822733c4607

Answer 2

只是不要按项目类型分组(也不要选择没有分组功能的项目)。

SELECT EMPID,         
       TOTAL_HOURS = SUM(HOURS), -- Just for cross-check
       PROJ = CASE WHEN SELECT (COUNT (DISTINCT PROJ_TYPE) 
                        FROM TABLE1 E 
                        WHERE E.EMPID = EE.EMPID 
                        GROUP BY E.EMPID) > 1 
                   THEN 'DEV/TEST' 
                   ELSE SELECT (DISTINCT PROJ_TYPE 
                                FROM TABLE1 TE 
                                WHERE TE.EMPID = EE.EMPID 
                                GROUP BY E.EMPID) 
              END,
     SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) AS DEV, 
     SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) AS TEST
FROM TABLE1 EE
GROUP BY EMPID

它不雅致，但应该能用

Answer 3

也许这是：

    with cte as (select EMPID,PROJ_TYPE, SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV, 
    SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
    FROM TABLE1
    GROUP BY EMPID, PROJ_TYPE)

    select t1.empID, isnull(t1.proj,'') + '/' + isnull(t2.proj,'') as proj, t1.hours, t2.hours)
    from cte t1
    full outer join cte t2 on t1.empID = t2.empID
    where isnull(t1.proj,'Dev') = 'Dev'
    and isnull(t2.proj, 'Test') = 'Test'