How can this resultset be achieved through a query?

[yehong]

I am using SQL Server to write a simple select query that should fetch all records on same row, instead of multiple rows. For example:
The data is:
ID PlanName Year
1001 AP_Q1 2006
1001 AP_Q2 2006
1001 AP_Q3 2006
1001 AP_Q4 2006

And I want this out put:
ID PlanName1 PlanName2 PlanName3 PlanName4 PlanYear
1001 AP_Q1 AP_Q2 AP_Q3 AP_Q4 2006

How can this be achived with SELECT statement?
Thanks in adavnce for any ideas.

 

[rac2]

Do you mean to go from this
ID PlanName Year Goal
1001 AP_Q1 2006 25
1001 AP_Q2 2006 35
1001 AP_Q3 2006 35
1001 AP_Q4 2006 55

to this?
ID PlanName1 PlanName2 PlanName3 PlanName4 PlanYear
1001 25 35 35 55 2006


Because there is no need for a query to produce the result you posted, and unless there is some value like AP_Q1 that indicates which row you wish to place under PlanName1, there is no solution.

SELECT a.ID, a.PlanName AS "PlanName1", 
       b.PlanName AS "PlanName2" 
       a.Year 
FROM MyTable a 
LEFT JOIN MyTable b ON b.ID = a.ID 
                AND b.PlanName = 'AP_Q2' 
WHERE a.PlanName = 'AP_Q1'

or

SELECT a.ID, a.Goal AS "PlanName1", 
       b.Goal AS "PlanName2", 
       a.Year
FROM MyTable a 
LEFT JOIN MyTable b ON b.ID = a.ID 
                AND b.PlanName = 'AP_Q2' 
WHERE a.PlanName = 'AP_Q1' 
ORDER BY a.Year, a.ID

In any case the key idea is to join the table to itself and use a condition that picks out the rows needed for each repeated column.