报表-横向拓展重复标题拓展 该文章主要介绍在做报表时遇到的横向拓展的问题，并针对这个问题的一种解法，仅供参考，该文章主要从目标展示样式，期初处理时遇到的问题，以及后期如何解决这些问题的方向介绍 报表最终需求展示样式： 期初设计 数据集：数据中表分为<主表>和<子表>，<主表>包括”工厂”和“工单号”的信息，<子表>包括”工单号”，“鼓风机编号”，“鼓风机频率”的信息 ```sql -- Design1 WITH temp1 AS( SELECT 'GD001' AS 工单号,'没品' AS 工厂 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'没品' AS 工厂 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'没品' AS 工厂 FROM DUAL  UNION ALL  SELECT 'GD004' AS 工单号,'没品' AS 工厂 FROM DUAL  ) SELECT t1.* FROM temp1 t1 -- Design2 WITH temp1 AS ( SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD004' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  ) SELECT t1.* FROM temp1 t1 ``` 报表设计： 期初设计展示：经过调试，发现标题不跟着数据移动，所以无法满足需求。   新的设计方案 上面的方式中，数据已经能够正确展示，但是标题未能横向拓展，所以标题能不能和数据一样展示在数据集中，和数据一样拓展。 新的数据集 ```sql -- Design1 不变 -- Design2 WITH temp1 AS ( SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  ) SELECT t1.*,'鼓风机编号' AS 编号,'鼓风机频率' AS 频率 FROM temp1 t1 ```   报表设计:C1,C2,D1,D2上父格无;C1左父格B1;D1左父格C1;C2左父格B2;D2左父格C2;B1,B2的上父格左父格都为无,A2,C2数据设置为列表，A2,B2拓展方向纵向，C1,C2,D1,D2拓展方向横向，C2设置过滤工单号=B2。 报表设计展示：发现标题拓展多了……那么再设计，拓展根据Design2中查询的数据里，工单号最多有几个鼓风机编码，然后根据这个数据展示 重新调整数据集Design2,获取每个工单有多少鼓风机号。 ```sql WITH temp1 AS ( SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL  UNION ALL  SELECT 'GD004' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL  ) SELECT t1.*,'鼓风机编号' AS 编号,'鼓风机频率' AS 频率,t2.NUMB_MAX FROM temp1 t1 INNER JOIN (SELECT t1.工单号,COUNT(0) AS NUMB_MAX FROM temp1 t1 GROUP BY t1.工单号) t2 ON t1.工单号=t2.工单号 ``` 报表调整。C1筛选前N项，添加公式;MAX(UNIQUEARRAY(Design2.select(NUMB_MAX)))   报表展示：符合需求：文件好像上传不了，放弃了