英文:
Reorganizing SQL query around specific row values
问题
我正在寻找一种更好地显示这个SQL查询结果的方法。目前，我的查询看起来像这样。
| 员工编号 | 预估重量 |
|---|---|
| A | 2 |
| B | 3 |
| A | 1 |
| A | 4 |
| B | 9 |
| C | 6 |
但我希望它看起来像这样
| A | B | C |
|---|---|---|
| 2 | 3 | 6 |
| 1 | 9 | |
| 4 |
有什么好的方法可以做到这一点？
我尝试过使用数据透视表和我最终将在RDL报告中使用的矩阵小部件，但要么所有重量都成为子标题，要么只有每个重量的最大值或最小值。
英文:
I'm looking for a way to better display this SQL query result. Right now, my query looks like this.
| EmployeeCode | EstimatedWeight |
|---|---|
| A | 2 |
| B | 3 |
| A | 1 |
| A | 4 |
| B | 9 |
| C | 6 |
But I'd like it to look like this
| A | B | C |
|---|---|---|
| 2 | 3 | 6 |
| 1 | 9 | |
| 4 |
What would be a good way to do this?
I've tried using pivot tables and the matrix widget in the RDL report I'll ultimately be using this in but I either end up with all of the weights as subheaders or with just the maximum or minimum of each weight.
答案1
得分: 0
对于一组固定的员工代码，通常您会使用 row_number() 枚举每个员工的权重，然后在权重等级分组的同时使用条件聚合进行数据透视。
请注意，如果您希望对权重进行特定的排序（而不仅仅是对值进行简单的升序或降序排序），那么您需要另外一列（或列的元组）来定义该排序；我假设使用了列 id。
您没有提供您正在使用哪个数据库。在标准的 ANSI SQL 中，可以这样写：
select
max(case when employee_code = 'A' then estimated_weight end) a,
max(case when employee_code = 'B' then estimated_weight end) b,
max(case when employee_code = 'C' then estimated_weight end) c
from (
select t.*,
row_number() over(partition by employee_code order by id) rn
from mytable t
) t
group by rn
如果您希望查询根据实际在表中可用的员工代码动态生成列，那么您需要使用动态 SQL，这更复杂，而且完全取决于供应商。
英文:
For a fixed list of employee codes, you would typically enumerate the weights of each employee with row_number(), then pivot with conditional aggregation (while grouping on the weight rank).
Note that if you want a specific order for the weights (other than a simple ascending or descending sort on the values), then you need another column (or tuple of columns) to define that ordering; I assumed column id.
You did not tell which database you are running. In standard ANSI SQL, this would be:
select
max(case when employee_code = 'A' then estimated_weight end) a,
max(case when employee_code = 'B' then estimated_weight end) b
max(case when employee_code = 'C' then estimated_weight end) c
from (
select t.*,
row_number() over(partition by employee_code order by id) rn
from mytable t
) t
group by rn
If you want a query that dynamically generates columns depending on the employee codes actually available in the table, then you need dynamic SQL - which is more complex, and totally vendor-dependent.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论