Ungrouping effect?
I have a dynamic set of data X of the form:
----------------------------------
x.id | x.allocated | x.unallocated
----------------------------------
foo | 2 | 0
bar | 1 | 2
----------------------------------
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 | un开发者_如何学JAVAallocated
----------------------------------
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.
If you have a numbers table in your database, you could use that to help get your results. In my database, I have a table named Numbers with a Num column.
Declare @Temp Table(id VarChar(10), Allocated Int, UnAllocated Int)
Insert Into @Temp Values('foo', 2, 0)
Insert Into @Temp Values('bar',1, 2)
Select T.id,'Allocated'
From @Temp T
Inner Join Numbers
On T.Allocated >= Numbers.Num
Union All
Select T.id,'Unallocated'
From @Temp T
Inner Join Numbers
On T.unAllocated >= Numbers.Num
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
This answer is just to ping back to G Mastros and doesn't need any upvotes. I thought he would appreciate a performance boost to his already superior query.
SELECT
T.id,
CASE X.Which WHEN 1 THEN 'Allocated' ELSE 'Unallocated' END
FROM
@Temp T
INNER JOIN Numbers N
On N.Num <= CASE X.Which WHEN 1 THEN T.Allocated ELSE T.Unallocated END
CROSS JOIN (SELECT 1 UNION ALL SELECT 2) X (Which)
精彩评论