How to Update row to differenct column

Jane · December 10th, 2004, 03:30 AM

Hi,

I have a table as this

whs item_no cust_no seq_no value
11 A001 a001 1 1000
11 a001 a001 2 2000
11 a001 a001 3 3000

i need to update value to this table as this

whs item_no cust_no value1 value2 value3
11 a001 a001 1000 2000 3000

what are SQL statement should take ?

Thanks :)

Jane

Anantsharma · December 10th, 2004, 07:51 AM

HI,

If you know that the Max Seq_no is 3 then here is the query for you

---------------------------------------
SELECT whs,item_no,cust_no,
    Value1= ISNULL((SELECT Value FROM TBL WHERE seq_no = 1 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value2= ISNULL((SELECT Value FROM TBL WHERE seq_no = 2 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value3= ISNULL((SELECT Value FROM TBL WHERE seq_no = 3 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

FROM TBL a
GROUP BY col1,col2
-----------------------------------

If you don't know the max of seq_no, you may want to declare a variable and store the MAX(SEQ_NO) with grouping on whs,item_no,cust_no. Then u can use a WHILE LOOP to generate the above query. Same Logic will be working. You can Generate teh SQL Statement dynamically storing into a Varchar variable and execute one by one using UNION ALL .

Hope you got my point.

Gud luck

B. Anant

Anantsharma · December 10th, 2004, 07:53 AM

HI again,

Please remove the '," from last colum...here it is---------
---------------------------------

SELECT whs,item_no,cust_no,
    Value1= ISNULL((SELECT Value FROM TBL WHERE seq_no = 1 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value2= ISNULL((SELECT Value FROM TBL WHERE seq_no = 2 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value3= ISNULL((SELECT Value FROM TBL WHERE seq_no = 3 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0)

FROM TBL a
GROUP BY col1,col2
-------------------------------------

B. Anant

Anantsharma · December 10th, 2004, 07:55 AM

Hey,

Sorry, again I forgot something. This one is correct
--------------------------------------

SELECT whs,item_no,cust_no,
    Value1= ISNULL((SELECT Value FROM TBL WHERE seq_no = 1 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value2= ISNULL((SELECT Value FROM TBL WHERE seq_no = 2 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

    Value3= ISNULL((SELECT Value FROM TBL WHERE seq_no = 3 AND whs=a.whs and item_no=a.item_no and cust_no=a.cust_no),0),

FROM TBL a
GROUP BY whs,item_no,cust_no

B. Anant

Jane · December 12th, 2004, 08:55 PM

Hi,
in fact i have two table

table A is as below:

whs item_no cust_no seq_no value
11 A001 a001 1 1000
11 a001 a001 2 2000
11 a001 a001 3 3000

then Table B is the value from table A update to Table B';
whs item_no cust_no value1 value2 value3
11 a001 a001 1000 2000 3000

is using the same SQL command as you recommended?

pls advised

Thanks

Jane