反转/吹灭一个 GROUP BY [英] Reverse/Blow out a GROUP BY

问题描述

我正在处理按项目编号和计数分组的数据.每条记录都有一个 count >2 需要分解为单个记录，并在该级别与不同的数据集进行比较.

I am working with data that is grouped by item number with counts. Each record with a count > 2 needs to be blown out into individual records and compared at that level to a different set of data.

数据看起来是这样的(它卡在这种格式.这是客户可以发送的唯一方式.):

The data looks like this (It is stuck in this format. This is the only way the customer can send it.):

OwnerNumber ItemCode    ItemNumber  CountOfItems
1234    Item1   Item1-001   3
1234    Item1   Item1-002   1
1234    Item1   Item1-003   2
1234    Item2   Item2-001   1

而且我需要这样格式化的数据(动态地没有对 CountOfItems 的值进行硬编码):

And I need the data formatted like this (dynamically without hardcoding for value of CountOfItems):

OwnerNumber ItemCode    ItemNumber  
1234    Item1   Item1-001
1234    Item1   Item1-001
1234    Item1   Item1-001
1234    Item1   Item1-002
1234    Item1   Item1-003
1234    Item1   Item1-003
1234    Item2   Item2-001

出于某种原因，我无法以一种干净的方式(或任何方式)来做到这一点.

For some reason I just can't wrap my head around a clean way to do this (or any way).

推荐答案

您可以使用公共表表达式进行管理

You can manage with a Common Table Expression

WITH CTE AS (
    SELECT OwnerNumber,ItemCode,ItemNumber,CountOfItems FROM table

    UNION ALL SELECT OwnerNumber,ItemCode,ItemNumber,CountOfItems-1
    FROM CTE
    WHERE CountOfItems >= 2
)
SELECT OwnerNumber,ItemCode,ItemNumber
FROM CTE
ORDER BY ItemNumber
OPTION (MAXRECURSION 0);

添加了 MAXRECURSION 以处理 Dev_etter 指出的 CountOfItems 超过默认最大递归数的情况

Added MAXRECURSION to handle situations where CountOfItems exceeds default max recursions as pointed out by Dev_etter

这篇关于反转/吹灭一个 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！