how can this resultset be achived through a query? 1

[yehong]

(sorry... I posted this topic earlier in a wrong forum and I am copying it here.)

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.

 

[Qik3Coder]

are the number of rows (AP_Q1...) fixed? As in, you will have a max number of rows?

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[mrdenny]

You want to use a cross tab query.

select ID,
   max(case when PlanName = 'AP_Q1' then PlanName end) 'PlanName1',
   max(case when PlanName = 'AP_Q2' then PlanName end) 'PlanName2',
   max(case when PlanName = 'AP_Q3' then PlanName end) 'PlanName3,
   max(case when PlanName = 'AP_Q4' then PlanName end) 'PlanName4,
   PlanYear
from table
group by ID, PlanYear

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yehong]

Thanks. To answer Qik3Coder, the number of rows is fixed but the planname is not fixed as I put in my post. Although it ends with 1,2,3, and 4.

And mrdenny I am going to give it a try by using PlanName Like '%1' as plannames are not constants but they do end with 1,2 , 3 and 4.

 

[mrdenny]

Then my query will work if changed the way that you posted.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qik3Coder]

so i am assuming it's some variation of

[planName]+'_'+[quarter]

if the column Count is fixed (as in 4 qtrs in a year)then you can modify MrDenny's query so that it looks like:

select ID,
   max(case when PlanName like '%1' then PlanName end) as 'Qtr 1 Plan',
   max(case when PlanName like '%2' then PlanName end) as 'Qtr 2 Plan',
   max(case when PlanName like '%3' then PlanName end) as 'Qtr 3 Plan',
   max(case when PlanName like '%4' then PlanName end) as 'Qtr 4 Plan',
   PlanYear
from table
group by ID, PlanYear

Sidebar:
Denny, are you doing the group by to merely collapse the result set

from:
Q- 1, Q- 2, Q- 3, Q- 4
----------------------
ap_1, ap_2, ap_3, ap_4
ap_1, ap_2, ap_3, ap_4
ap_1, ap_2, ap_3, ap_4
ap_1, ap_2, ap_3, ap_4

to:

Q- 1, Q- 2, Q- 3, Q- 4
----------------------
ap_1, ap_2, ap_3, ap_4

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[mrdenny]

The group by is required because of the max() around the case statements. The max()'s are required to collapse the result set down.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yehong]

Denny, so far the query has worked perfectly. Thanks

 

[Qik3Coder]

then give that boy a star.


MrDenny, i figured as much, i just wanted to verify, without testing.

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[yehong]

done. But if you guys don't mind. Will the same query work if we don't know how the planname is going to look like but know each ID will have 4 plan names? In above example we know that it ends with 1,2...4. How can we write a query for same data where we don't know the plan names?

 

[Qik3Coder]

Depends on how you are executing your query.

if you are executing a stored proc, then you will most likely need either an extra parameter, or multiple stored procs.

you could either have multi if's in the statement, or do a sub select in your code to retrieve the correct value:

select ID,
   max(case when PlanName like '%'+(select plan_id from plan_ids where planId = ID) then PlanName end) as 'Qtr 1 Plan',
   PlanYear
from table
group by ID, PlanYear

be careful that you only return one value for that, or things could get messy

if you are building your code on the fly as in:

strSQL = "Select"
strSQL = strSQL + " col1, col2"

then you can just concat your values as you go.

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.