解组效果?

2021-09-10 00:00:00 sql tsql sql-server

我有一组动态数据X，形式如下:

I have a dynamic set of data X of the form:

----------------------------------
x.id | x.allocated | x.unallocated
----------------------------------
foo  | 2           | 0
bar  | 1           | 2
----------------------------------

我需要得到 Y 的结果(顺序不重要):

And I need to get to a result of Y (order is unimportant):

----------------------------------
y.id | y.state
----------------------------------
foo  | allocated
foo  | allocated
bar  | allocated
bar  | unallocated
bar  | unallocated
----------------------------------

我有一个基于 UTF 的解决方案，但我正在寻找超高效率，所以我想知道是否有一种基于语句的非程序方式来获得这种取消分组"效果?

I have a UTF based solution, but I'm looking for hyper-efficiency so I'm idly wondering if there's a statement based, non-procedural way to get this kind of "ungroup by" effect?

感觉像是一个转轴，但我的大脑现在无法到达那里.

It feels like an unpivot, but my brain can't get there right now.

推荐答案

使用 Sql Server 2005，UNPIVOT 和 CTE 你可以试试喜欢

Using Sql Server 2005, UNPIVOT, and CTE you can try something like

DECLARE @Table TABLE(
        id VARCHAR(20),
        allocated INT,
        unallocated INT
)

INSERT INTO @Table SELECT 'foo', 2, 0
INSERT INTO @Table SELECT 'bar', 1, 2

;WITH vals AS (
        SELECT  *
        FROM    
        (
            SELECT  id,
                    allocated,
                    unallocated
            FROM    @Table
        ) p
        UNPIVOT (Cnt FOR Action IN (allocated, unallocated)) unpvt
        WHERE   Cnt > 0
)
, Recurs AS (
        SELECT  id,
                Action,
                Cnt - 1 Cnt
        FROM    vals
        UNION ALL
        SELECT  id,
                Action,
                Cnt - 1 Cnt
        FROM    Recurs
        WHERE   Cnt > 0

)
SELECT  id,
        Action
FROM    Recurs
ORDER BY id, action

相关文章