sql - 将现有数据表重新格式化为单行条目？

Question

我想从现有 SQL Server 表中获取job_number单行上同一字段的所有条目。例如，我想取下表：

+--------------+------------+-------------------+-------------+------------+--------------+
| Company_Code | Job_Number | User_Def_Sequence | Alpha_Field | Date_Field | Amount_Field |
+--------------+------------+-------------------+-------------+------------+--------------+
| ABC          | 02-0294-00 |            000001 |             | NULL       |     0.000000 |
| ABC          | 02-0294-00 |            000003 |             | NULL       |     0.000000 |
| ABC          | 02-0294-00 |            000006 |             | NULL       |     0.000000 |
| ABC          | 02-0418-00 |            000001 |             | NULL       |     0.000000 |
| ABC          | 02-0418-00 |            000002 |             | NULL       |     0.000000 |
| ABC          | 02-0418-00 |            000003 | 15-02-0065  | NULL       |     0.000000 |
| ABC          | 02-0424-00 |            000003 | 15-02-0095  | NULL       |     0.000000 |
| ABC          | 02-0431-00 |            000003 | 15-02-0095  | NULL       |     0.000000 |
| ABC          | 02-0435-00 |            000003 | 15-02-0102  | NULL       |     0.000000 |
+--------------+------------+-------------------+-------------+------------+--------------+

并将其转换为如下内容：

+--------------+------------+--------+------------+--------+----------+--------+---------+--------+----------+--------+------------+--------+----------+
| Company_Code | Job_Number | UDS_1  |  Alpha_1   | Date_1 | Amount_1 | UDS_2  | Alpha_2 | Date_2 | Amount_2 | UDS_3  |  Alpha_3   | Date_3 | Amount_3 |
+--------------+------------+--------+------------+--------+----------+--------+---------+--------+----------+--------+------------+--------+----------+
| ABC          | 02-0294-00 | 000001 |            | NULL   | 0.000000 | 000003 |         | NULL   | 0.000000 | 000006 |            | NULL   | 0.000000 |
| ABC          | 02-0418-00 | 000001 |            | NULL   | 0.000000 | 000002 |         | NULL   | 0.000000 | 000003 | 15-02-0065 | NULL   | 0.000000 |
| ABC          | 02-0424-00 | 000003 | 15-02-0065 | NULL   | 0.000000 |        |         |        |          |        |            |        |          |
| ABC          | 02-0431-00 | 000003 | 15-02-0095 | NULL   | 0.000000 |        |         |        |          |        |            |        |          |
| ABC          | 02-0435-00 | 000003 | 15-02-0102 | NULL   | 0.000000 |        |         |        |          |        |            |        |          |
+--------------+------------+--------+------------+--------+----------+--------+---------+--------+----------+--------+------------+--------+----------+

编辑：每个作业编号可能有多个User_Def_Sequence变体，需要将它们连同它们的值一起附加到同一行。

实现这一目标的最佳方法是什么？

标签： sqlsql-server

Answer 1

我想你想要聚合：

select Company_Code, Job_Number,
       min(User_Def_Sequence),
       min(Alpha_Field),
       min(Alpha_Field),
       min(Amount_Field),
       . . .
from t
group by Company_Code, Job_Number;