我有一组动态的数据X：
---------------------------------- 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的解决方案,但我正在寻找超效率,所以我想知道是否有一种基于语句,非程序性的方式来获得这种“取消组合”的效果？
感觉就像一个不透明,但我的大脑现在无法到达那里.
解决方法
使用sql Server 2005,UNPIVOT和
CTE,您可以尝试类似的东西
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
