sql – 取消组合效果？

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

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

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

我有一个基于UTF的解决方案,但我正在寻找超效率,所以我想知道是否有一种基于语句,非程序性的方式来获得这种“取消组合”的效果？

感觉就像一个不透明,但我的大脑现在无法到达那里.

解决方法

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,Cnt - 1 Cnt
        FROM    Recurs
        WHERE   Cnt > 0

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!