sql - 如何将特定列值转换为 SQL 中一组值的标题？

Question

我有下表：

表A

 +-----------+--------+--------------+
  | tableA_id |  code  | Department   |
  +-----------+--------+--------------+
  |         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    |
  +-----------+--------+--------------+

我想使用以下格式的 SQL 查询来转换它

 +--------------+ 
 | Department   |
 +--------------+
 |  Code A      |
 +--------------+
 |  Science 1   |
 |  Science 2   |
 |  Science 3   |
 |  Science 4   |
 +--------------+ 
 |  Code  B     |
 +--------------+
 | Science 1    |
 | Science 2    |
 | Science 3    |
 | Science 4    |
 +--------------+
 |  Code C      |
 +--------------+
 | Science 1    |
 | Science 2    |
 | Science 3    |
 +--------------+

基本上我希望它按代码分组，但需要在每个组的顶部显示代码，并且列中的值不固定它是动态出现的。

标签： sqlsql-server

Answer 1

这可以通过group by rollup来实现。

初始解决方案

-- 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