問題描述

我有一組動態數據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

這篇關于解組效果?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！