Can I write a query and use column headings as values

[tongaman]

I have a table with the following fields

Product New_Customer Old_Customer Potential_Customer
Car1 100 200 300
Car2 200 300 400
Car3 500 600 700

I would like to write a query that would make a table that looks like the one below.

Product Customer Price
Car1 New 100
Car2 New 200
Car3 New 500
Car1 Old 200
Car2 Old 300
Car3 Old 600
Car1 Potential 200
Car2 Potential 300
Car3 Potential 600

Any help is much appreciated!

 

[PCLewis]

If your table was called Table2 then..

SELECT Product,'New' as customer, New_cust as price
FROM Table2

UNION ALL

SELECT Product, 'Old' as customer, old_cust as price
FROM Table2

UNION ALL

SELECT Product, 'Potential' as customer, potential_cust as price
FROM Table2

You'd have to write code to make it a dynamic kind of query though.

 

[tongaman]

PCLEWIS,

You are a champ, this code is exactly what I waslooking for.

Cheers,
Tongaman.