SQL Server Performance

Multiplying row occurences by the value of a given field

Discussion in 'SQL Server 2005 General Developer Questions' started by EMoscosoCam, Oct 25, 2008.

  1. EMoscosoCam Member

    Hello
    Suppose that I have a table StockMovement(#ItemCode char(8), Date datetime, Quantity int). I would like that if the Quantity field value is greater that one, then in a given resultset that row would actually appear more than once, depending of the Quantity field value.
    How can I accomplish that with a single SELECT statement?
    Thanks a lot.
    EMoscosoCam, Oct 25, 2008
    #2

  2. FrankKalis Moderator

    Can you post some sample data of what you expect?
    FrankKalis, Oct 28, 2008
    #3

  3. Madhivanan Moderator

    [quote user="EMoscosoCam"]
    Hello
    Suppose that I have a table StockMovement(#ItemCode char(8), Date datetime, Quantity int). I would like that if the Quantity field value is greater that one, then in a given resultset that row would actually appear more than once, depending of the Quantity field value.
    How can I accomplish that with a single SELECT statement?
    Thanks a lot.
    [/quote]
    I think you need to cross join with number table
    Madhivanan, Oct 29, 2008
    #4

  4. Adriaan New Member

    Hm, usually people want to suppress duplicates, rather than "create" them.
    The usual problem occurs when people JOIN a one-table and a many-table, filtering on the many-table, but showing only details from the one-table. To avoid showing duplicates from the one-table, you have to add the DISTINCT keyword, or a GROUP BY clause (or change join on the many-table to a correlated subquery in the WHERE clause).
    So if your current SELECT has DISTINCT or GROUP BY, I'd suggest removing those items to see where it gets you.
    Adriaan, Oct 29, 2008
    #5

  5. Adriaan New Member

    Madhivanan - that number table should have one row with value 1, two rows with value 2, etc. An INNER JOIN will do just fine, just hoping there aren't too many different, or too large, quantities.
    uh - well, at least not a CROSS JOIN, but something like this:
    FROM MyTable INNER JOIN tblNumber ON MyTable.Quantity >= tblNumber.Number
    Adriaan, Oct 29, 2008
    #6

  6. Madhivanan Moderator

    [quote user="Adriaan"]
    Madhivanan - that number table should have one row with value 1, two rows with value 2, etc. An INNER JOIN will do just fine, just hoping there aren't too many different, or too large, quantities.
    uh - well, at least not a CROSS JOIN, but something like this:
    FROM MyTable INNER JOIN tblNumber ON MyTable.Quantity >= tblNumber.Number
    [/quote]
    Well Adriaan. By cross join I meantdeclare
    @t table(data varchar(100), quantity int)insert
    into @t(data, quantity)
    select 'test1',1 union all
    select 'test2',4 union all
    select 'test3',2 select
    * from @tselect
    t1.* from @t as t1 cross join
    (select number from master..spt_values where type = 'P') as t2
    where t1.quantity>t2.number
    But Inner Join also can be used
    Madhivanan, Oct 30, 2008
    #7

  7. Adriaan New Member

    I love these shortcuts, but usually get stumped by limitations - the maximum value of that query on spt_values is 256. Probably sufficient for now, but for how long ...
    Adriaan, Oct 30, 2008
    #8

  8. FrankKalis Moderator

    Btw, this has been extended to, I think 2048 or so, in SQL Server 2005, which is also not the most recent version... [:)]
    FrankKalis, Oct 30, 2008
    #9

  9. Adriaan New Member

    Oops, exposed again as a SQL 2000 die-hard ...
    Adriaan, Oct 30, 2008
    #10

  10. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>Btw, this has been extended to, I think 2048 or so, in SQL Server 2005, which is also not the most recent version... <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"><BR></P><P>[/quote]</P><P>Yes it has values between 0 and 2047</P>
    Madhivanan, Oct 30, 2008
    #11

Share This Page