Creating colums from a query

[gregm1978]

Here is the table structure and I need to query the table based on A_Value field to get partial data from two rows (data stored in seperate rows based on A_Value) to make one row, i.e.

Name Date A_Value X_Value
ABC 11-26-01 10 98%
ABC 11-26-01 15 94%
BCD 11-26-01 10 92%
BCD 11-26-01 15 93%
CDE 11-26-01 10 90%
CDE 11-26-01 15 91%

I need to the query to return the output as follows:

Name Date XY_Value YZ_Value
ABC 11-26-01 98% 94%
BCD 11-26-01 92% 93%
CDE 11-26-01 90% 91%

 

[rac2]

SELECT a.Name, a.Date, a.X_Value AS "XY_Value", b.X_Value AS "YZ_Value"
FROM myDoppleTable a
JOIN myDoppleTable b ON a.Name = b.Name
WHERE a.A_Value = 10 AND b.A_Value = 15

 

[tlbroadbent]

Try a query like this.

Select
a.NameCol,
a.DateCol,
a.X_value As XY_Value,
b.X_value As YZ_Value
From Table_Name As a
Inner Join Table_Name As b
On a.NameCol=b.NameCol
Where a.A_Value<b.A_value

NOTE: Don't use Name and Date as column names as those are reserved words in many RDMS. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.