if qty_requested =8 then display the information 8 times.

Question

Post reply

if qty_requested =8 then display the information 8 times.

twdavis-893252

Ten Centuries

Points: 1195
More actions
July 23, 2009 at 3:32 pm

#133214

I have a table that shows the order information. I want to create a statement that takes the qty_requested and display the infromation that many times for each line. So if the customer order 8 of a inv_item the name and address etc.. would be displayed 8 times if the next line the customer order 5 inv_item the name and address etc.. would be displayed 5 times etc....
select C.CUSTOMER_PO,A.LOAD_ID,A.ORDER_NO,A.INV_ITEM_ID,A.QTY_REQUESTED,A.SHIP_CUST_NAME1, B.ADDRESS1, B.CITY,B.STATE, B.POSTAL
from PS_IN_DEMAND A, PS_CUST_ADDRESS B , PS_ORD_HEADER C
WHERE A.SHIP_TO_CUST_ID=B.CUST_ID
AND A.ORDER_NO=C.ORDER_NO
AND A.CUST_ID='0000080019'
AND IN_FULFILL_STATE NOT IN ('90','70')

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

If I understand you you could use a Numbers/Tally table and join to that.. For example:-- SET UP

DECLARE @Numbers TABLE (Num INT)

INSERT @Numbers

SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

UNION ALL SELECT 8

--

DECLARE @MyTable TABLE (Val INT)

INSERT @MyTable

SELECT 3

UNION ALL SELECT 4

-- Query 1

SELECT *

FROM @MyTable AS T

FULL OUTER JOIN @Numbers AS N

ON N.Num <= T.Val

WHERE T.Val IS NOT NULL

-- Query 2

SELECT *

FROM @MyTable AS T

CROSS JOIN @Numbers AS N

WHERE N.Num <= T.Val

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Could you demonstrate what you mean? I'm sort of at a loss here and would really like you to show us what the output of your query would look like.

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 3

02961941/0296194245382059376064namestreet addresscityOH43068

02961941/0296194245382059376087namestreet addresscityOH43068

02961941/0296194345382059376087namestreet addresscityOH43068

02961941/0296194445382059376087namestreet addresscityOH43068

02961941/0296194545382059376087namestreet addresscityOH43068

02961941/0296194645382059376087namestreet addresscityOH43068

02961941/0296194745382059376087namestreet addresscityOH43068

02961941/0296194845382059376087namestreet addresscityOH43068

02961941/0296194945382059376093namestreet addresscityOH43068

02961941/0296195045382059376093namestreet addresscityOH43068

02961941/0296195145382059376093namestreet addresscityOH43068

this is what I would like it to look like

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 4

the po has the / in it and the rest is LOAD_ID,ORDER_NO,INV_ITEM_ID,QTY_REQUESTED,SHIP_CUST_NAME1,ADDRESS1,CITY,STATE,POSTAL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

twdavis (7/23/2009)
02961941/0296194245382059376064namestreet addresscityOH43068
02961941/0296194245382059376064namestreet addresscityOH43068
02961941/0296194245382059376064namestreet addresscityOH43068
02961941/0296194245382059376064namestreet addresscityOH43068
02961941/0296194245382059376087namestreet addresscityOH43068
02961941/0296194345382059376087namestreet addresscityOH43068
02961941/0296194445382059376087namestreet addresscityOH43068
02961941/0296194545382059376087namestreet addresscityOH43068
02961941/0296194645382059376087namestreet addresscityOH43068
02961941/0296194745382059376087namestreet addresscityOH43068
02961941/0296194845382059376087namestreet addresscityOH43068
02961941/0296194945382059376093namestreet addresscityOH43068
02961941/0296195045382059376093namestreet addresscityOH43068
02961941/0296195145382059376093namestreet addresscityOH43068
this is what I would like it to look like

I saw that you add info in the following post, but that really doesn't help. Column Heades over the data would be much more helpful. You can also put the data inside a [ code="text" ] [ /code ] block (be sure not to include the spaces inside the square brackets.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

Lamprey has the right idea with the Tally table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 7

I believe that Lamprey's solution with the Tally table will do what you need. For more information on what a Tally table is and how it can be used to replace certain loops, please refer to the following article:

http://www.sqlservercentral.com/articles/T-SQL/62867/

The Tally table has many uses that not only make writing T-SQL a whole lot easier for certain things, but it makes the code a whole lot faster than using Cursors and While Loops.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)