select query (quite urgent)............

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select query (quite urgent)............

Author

Topic

mk07
Starting Member

4 Posts

Posted - 2009-03-28 : 02:48:47

Hi Friends,

in my application i need to do the following..

i have a one tbl(named as tbl1) and have the records like the following

tbl1
id lotno qty type serialno
0 43243 8.0 test 123456
0 54236 8.7 result 123456
0 34535 8.8 return 123456

in the above table i have 3 type of record for the same serial no..so in that
i need to insert into these 3 types records into one row at another table

like the following..
tbl2
qty test(type of tbl1) result return
8.0 42243(lotno of tbl 1) 54236 34535

the above is output i need..

actully in this table(tbl2)all the coumns (based on tbl1 type)..so i need to select lotno on the first table(tbl1) based on the type and insert into one row like the above output table(tb2) and the qty is based type test.

and in my first table(tbl1) , here i showed only one serialno , but actually i have many serialno like that.. each serial no have 3 type os records and some have 2 types of records

what query will make this things..

i am using sql2000 datbase

and really quite urgent..

waiting for your valuable reply..

with regards,
mk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:59:39

[code]
SELECT serialno,
MAX(CASE WHEN type='test' THEN qty ELSE NULL END) AS qty,
MAX(CASE WHEN type='test' THEN lotno ELSE NULL END) AS test,
MAX(CASE WHEN type='result' THEN lotno ELSE NULL END) AS result ,
MAX(CASE WHEN type='return' THEN lotno ELSE NULL END) AS return
FROM Table
GROUP BY serialno
[/code]

mk07
Starting Member

4 Posts

Posted - 2009-03-28 : 10:09:05

Hi visakh16:

Thx a lot man..you have done good job for me..thx a lot..
really very good reply..
if u can pls send me ur mail id.take care and thx man

with regards,
Krish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:48:17

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources