ansaurus

Question

Ungrouping effect?

Answer 1

+5 A:

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

G Mastros 2010-08-12 15:32:13

Simple, elegant, but obviously breaks down at extreme number ranges unless you want to generate bigint.max rows in your table. Almost certainly the most efficient solution, but I dislike polluting my DB with support mechanisms.

annakata 2010-08-13 08:59:17

Polluting a DB with support mechanisms? Really? I think that's the definition of an index. Indexes exists to support your DB's querying performance. I bet you have a bunch of those in your database.

G Mastros 2010-08-13 12:48:44

@annakata - You're a real joker! Do you realize that the numbers in the OP's question are actually being translated into real rows in a real result set? Whatever the rowcount is going to be of the final result set, if the server and application can handle that, they can handle a numbers table to go along with it. Allowing for some unlikely colossal system, one table with 2^31-1 integer values in it is only about 8 Gb of data. Returning 2,147,483,647 rows is thus no problem, and I guarantee this solution will outperform your CTE by orders of magnitude.

Emtucifor 2010-08-25 23:40:48

@Emtucifor - Well you obviously didn't realise that *I am the OP*. I deeply object to creating independent structures in a domain which do not map to domain objects. This is the logical equivalent of static methods and helper classes imho, and I don't think the index comparison is relevant because indexes are not independant of their tables.

annakata 2010-08-26 06:57:56

@annakata - You're right. I missed that fact. Haha on me! You are of course entitled to your opinion. I just had to say my piece about why I thought it wasn't a well-founded one. In my perspective, satisfying theoretical idealism at the expense of simplicity and performance is, well, a mistake. I think of a numbers table as making up for a missing feature that ought to be in SQL Server: a function that returns a rowset of numbers within a certain range. This should be so easy for the engine to build and could be all in memory. In the meantime, it's a GREAT way for solving problems.

Emtucifor 2010-08-26 16:06:03

Actually, now that I think about it, why can't a numbers table be a domain object? It is the list of quantities that are possible to be allocated or unallocated. While it seems funny because it's just a list of numbers, it's a perfectly valid extension of the business information space.

Emtucifor 2010-08-26 16:08:14

@annakata - in thinking about this later, I realized I was unnecessarily rude to you. Sorry about that. I could have expressed myself less condescendingly.

Emtucifor 2010-08-27 21:01:22

Answer 2

+4 A:

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

astander 2010-08-12 15:37:36

I knew it was unpivotable :) Thanks.

annakata 2010-08-13 09:00:08

Answer 3

A:

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)

Emtucifor 2010-08-25 23:46:23

ansaurus

tags:

views:

answers:

Ungrouping effect?

related questions