How do I get multiple rows when value > 1

Possible Duplicate:
Select Records multiple times from table

I want to have my query return (multiple) rows for the value of TABLE_B.QTY.

  • Using Row_number() OVER(partition BY..) along with declaring local variables
  • Drop default constraint on a column in TSQL
  • Microsoft SQL Server Management Studio Rewrites my query incorrectly
  • Cross Server Transaction taking too long inside a procedure
  • How to get first login and last logout time from sql server?
  • Need to sort out by tables name
  • TABLE A
    SALESNR  ITEMNR LINENR
    100      B2001  1
    101      B2002  2
    102      A1021  3 
    
    TABLE B 
    LINENR   COLOR QTY
    1        WHITE  3  
    2        BLACK  1
    3        BROWN  8
    

    For instance, with the following query:

    SELECT    TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY
    FROM       TABLE_A  INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR
    

    I get:

    100    B2001   White   3
    

    What I need is:

    100    B2001   White   3
    100    B2001   White   3
    100    B2001   White   3
    

    Is there a way to do this?
    Can’t think of the right keywords to Google this…

    Thnx,

    Mike

    2 Solutions collect form web for “How do I get multiple rows when value > 1”

    This will work as long as QTY is less than 2047

    SELECT TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY 
    FROM TABLE_A
    INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR 
    INNER JOIN master..spt_values ON type = 'P' AND number < TABLE_B.QTY
    

    use this if QTY exceeds 2047:

    ;WITH a AS
    (
    SELECT TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY, 1 row
    FROM TABLE_A
    INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR 
    WHERE QTY > 0
    union all
    SELECT SALESNR, ITEMNR, COLOR, QTY, row+1 
    FROM a
    WHERE QTY > row 
    )
    SELECT SALESNR, ITEMNR, COLOR, QTY from a
    OPTION (MAXRECURSION 0) 
    

    The cross join won’t do it if you have one row in each table, which I interpret it as. I would suggest, if possible, to re-design your data model to solve this – or loop in code where you use this data.

    You can loop in the T-SQL if absolutely needed.

    regards, Olle

    Can’t seem to comment other peoples posts, just wanted to say, nice solution, to t-clausen.dk!

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.