Ungrouping effect?

Question

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  | unallocated
----------------------------------

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.

Answer 1

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