change data from one column into two in SQL

[Bebe]

I have a table that I am trying to generate a report for. In the table is a customer Id and a vendor Id. In the vendor ID I get two results for the same customer ID. A negative number before the order is processed and a postive number when the item has made full circle. I need to extract every matching customer ID with its corresponding negative and positive number:

right now if i do a search say on customer '10050' i get two vendor numbers on with a negative and one with a postive like this:

10050 -1000
10050 0

I really need it to be:

10050 -1000 0
10051 -1000 0

I need to return all records in a certain time frame not just '10050'

Do I need a temp table. An example would be nice - thank you!!!


This is done in SQL using and SQL database

[*Vince]

Hi,

I don't know if your are in VB or directly in SQL SERVER but I may have a solution for you...

Make a copy of your table (same field names...) and name it "temp".


1) INSERT INTO temp (SELECT * FROM Table1 WHERE
Vendor_ID < 0)

You have just splitted your data in two table with 1 beeing
still the original and the other having all -.

SELECT * FROM Table1 INNER JOIN temp ON Table1.CustumerID =
temp.CustumerID


not tested but I think it could work...

Hope I helped...

[Bebe]

Is it possible to use a subquery instead? doing a sort of self-join?

[Bebe]

I tried this, but I am getting an error that says error around the temp word or whatever any ideas?

I think this is on the right track; anyone know?