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

回答1:

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

回答2:

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

回答3:

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)