问题描述
我有一组动态数据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