Merging multiple columns into one

[Vrom]

hello guys.

i have a question i was hoping someone would be able to help me with. here is my problem:

i have a database with 3 tables, one called CustomerInfo, one called Invoices, and one called CreditNotes. when i link and join the three together i get a new table that has (and looks something like) this:
(please ignore the dashes, they are only used to preserve spacing)


CustomerID CustomerName TransactionType InvoiceValue CreditNoteValue
-----1----------John----------Invoice----------100--------------------
-----2----------Jane--------CreditNote-----------------------200------

what i was wondering is if it is possible to query the DB to get something like this:

CustomerID CustomerName TransactionType Value
----1----------John----------Invoice-------100
----2----------Jane---------CreditNote-----200

the idea is that i would like to merge the two columns (invoice & credit note together) into one Value column. since it is never possible for a TransactionType that is an Invoice to have a value in the CreditNoteValue field or a TransactionType that is a CreditNote to have a value in the InvoiceValue field, there is no possibility of a clash in the Value field (by clash i mean having values for both InvoiceValue and CreditNoteValue and not knowing which one to put in the Value field).

so, if this has made any sense to anyone, can it be done? i also understand that it may not be possible to do in one step and may require multiple sql queries or intermediate tables.

thanking you all in advance for your time. hope to hear from someone soon...

kind regards, Alex.

 

[mohammedhakkim]

hey alex
try this

select customerid,customername,transactiontype,case transactiontype when 'Invoice' then invoicevalue else creditnotevalue end as value from
Table1

hope this will help you alex

cheers
hakkim

 

[Vrom]

nice one hakkim. thanks a lot.

 

[cjard]

(please ignore the dashes, they are only used to preserve spacing)

You should use [code]...[/code] tags for that..

Also set the WYSIWYG editor font to Courier to make it easier to write your post (CODE tags always render in courier so if you write your post in courier, you get a better idea of the resulting look:

[FONT=Courier New]CustomerID CustomerName TransactionType InvoiceValue CreditNoteValue[/FONT]
[FONT=Courier New]1          John         Invoice         100[/FONT]
[FONT=Courier New]2          Jane         CreditNote                   200[/FONT]