How Can I change the data from row to coloumn

[inhisglory]

I have table 1 with this contains :

CUST TYPE VALUE
===============================
001 INV 100
002 INV 200
003 CN 400
004 CN 300
005 INV 500

I want to change table 1 to table 2 with this format :

CUST INV CN
=======================
001 100 0
002 200 0
003 0 300
004 0 400
005 500 0

You see that field name INV and CN I got from field tipe in table 1.

Thanks

 

[StevenK]

If you're only going to have 2 possibilities (namely INV or CN) then I would recommend using a CASE statement such that if the TYPE column contains 'INV' then you output the VALUE entry to the 'INV' column and if the TYPE column contains 'CN' then outpit the VALUE entry to the 'CN' column.
The SQL syntax will be something like :
SELECT T1.CUST,
'INV' = CASE
WHEN (T1.TYPE = 'INV') THEN T1.VALUE
WHEN (T1.TYPE <> 'INV') THEN 0
END,
'CN' = CASE
WHEN (T1.TYPE = 'CN') THEN T1.VALUE
WHEN (T1.TYPE <> 'CN') THEN 0
END
FROM Table1 T1

Hope that this proves useful (syntax may need to be verified)
Steve

 

[inhisglory]

Thanks Steven, its work