我有一组动态的数据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
