这可以通过分组汇总来实现。

初始解决方案

-- create sample data
declare @data table
(
    id int,
    code nvarchar(10),
    department nvarchar(20)
);

insert into @data (id, code, department) values
( 1, 'code A', 'Science 1'),
( 2, 'code B', 'Science 1'),
( 3, 'code A', 'Science 2'),
( 4, 'code C', 'Science 1'),
( 5, 'code B', 'Science 2'),
( 6, 'code A', 'Science 3'),
( 7, 'code C', 'Science 2'),
( 8, 'code B', 'Science 3'),
( 9, 'code A', 'Science 4'),
(10, 'code C', 'Science 3'),
(11, 'code B', 'Science 4');

单一结果查询：

-- solution
select coalesce(d.department, d.code) as 'Department'
from @data d
group by d.code, d.department with rollup
having grouping(d.code) = 0
order by d.code, d.department;

运行此查询以了解我是如何找到上一个解决方案的：

-- solution explained
select  grouping(d.department) as 'grouping_dep',
        d.department,
        grouping(d.code) as 'grouping_cod',
        d.code,
        coalesce(d.department, d.code) as 'Department',
        case
            when grouping(d.code) = 1 then 'aggregation across codes, filtered out with "having"'
            else 'select "department", replace with "code" when null'
        end as 'explanation'
from @data d
group by d.code, d.department with rollup -- group on every level and "roll up" the aggregations
order by d.code, d.department;

扩展解决方案

处理2个额外的列（基于注解中的其他输入）。

-- create sample data
declare @data table
(
    id int,
    code nvarchar(10),
    department nvarchar(20),
    colA nvarchar(10),
    colB nvarchar(10)
);

insert into @data (id, code, department, colA, colB) values
( 1, 'code A', 'Science 1', 'A1_A', 'A1_B'),
( 2, 'code B', 'Science 1', 'B1_A', 'B1_B'),
( 3, 'code A', 'Science 2', 'A2_A', 'A2_B'),
( 4, 'code C', 'Science 1', 'C1_A', 'C1_B'),
( 5, 'code B', 'Science 2', 'B2_A', 'B2_B'),
( 6, 'code A', 'Science 3', 'A3_A', 'A3_B'),
( 7, 'code C', 'Science 2', 'C2_A', 'C2_B'),
( 8, 'code B', 'Science 3', 'B3_A', 'B3_B'),
( 9, 'code A', 'Science 4', 'A4_A', 'A4_B'),
(10, 'code C', 'Science 3', 'C3_A', 'C3_B'),
(11, 'code B', 'Science 4', 'B4_A', 'B4_B');

由于分组的原因，您需要使用聚合函数（如 min 函数（此处使用）来选择值。

-- solution
select  coalesce(d.department, d.code) as 'Department',
        case when grouping(d.department)=1 then '' else min(d.colA) end as 'colA',
        case when grouping(d.department)=1 then '' else min(d.colB) end as 'colB'
from @data d
group by d.code, d.department with rollup
having grouping(d.code) = 0
order by d.code, d.department;

这将提供：

Department           colA       colB
-------------------- ---------- ----------
code A
Science 1            A1_A       A1_B
Science 2            A2_A       A2_B
Science 3            A3_A       A3_B
Science 4            A4_A       A4_B
code B
Science 1            B1_A       B1_B
Science 2            B2_A       B2_B
Science 3            B3_A       B3_B
Science 4            B4_A       B4_B
code C
Science 1            C1_A       C1_B
Science 2            C2_A       C2_B
Science 3            C3_A       C3_B

看起来像是表示层任务。一种方法是使用 cursor 选择不同的代码，并按代码顺序将记录添加到最终表中。

DECLARE @final TABLE (Department varchar(100))
 DECLARE @code varchar(10)

 DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR 
 SELECT DISTINCT code FROM @t t 

 OPEN cur
 FETCH NEXT FROM cur INTO @code

 WHILE @@FETCH_STATUS = 0
 BEGIN
    INSERT INTO @final SELECT @code 
    INSERT INTO @final 
    SELECT Department FROM @t t
    WHERE t.code = @code    

    FETCH NEXT FROM cur INTO @code  
 END

 CLOSE cur
 DEALLOCATE cur

 SELECT * FROM @final f

请在这里找到演示（包括额外的专栏）。