I am trying to convert 4 rows into a single column...
There are 27 Ptypes, 6 Regions, and 4 Tiers
Is there a way to convert the existing table into the desired table?

Existing.....
PType Region Tier Value
---------------------------------------------------------
RT01 R1 T1 RT1R1T1
RT01 R2 T1 RT1R2T1
RT01 R3 T1 RT1R3T1
RT01 R4 T1 RT1R4T1
RT01 R5 T1 RT1R5T1
RT01 R6 T1 RT1R6T1
RT01 R1 T2 RT1R1T2
RT01 R2 T2 RT1R2T2
RT01 R3 T2 RT1R3T2
RT01 R4 T2 RT1R4T2
RT01 R5 T2 RT1R5T2
RT01 R6 T2 RT1R6T2
etc

Desired
Ptype/TierR1 R2 R3 R4 R5 R6
-----------------------------------------------------------------
RT01/T1 RT1R1T1 RT1R2T1 RT1R3T1 RT1R4T1 RT1R5T1 RT1R6T1
RT01/T2 RT1R1T2 RT1R2T2 RT1R3T2 RT1R4T2 RT1R5T2 RT1R6T2
RT01/T3 RT1R1T3 RT1R2T3 RT1R3T3 RT1R4T3 RT1R5T3 RT1R6T3
RT01/T4 RT1R1T4 RT1R2T4 RT1R3T4 RT1R4T4 RT1R5T4 RT1R6T4
RT02/T1 RT2R1T1 RT2R2T1 RT2R3T1 RT2R4T1 RT2R5T1 RT2R6T1
RT02/T2 RT2R1T2 RT2R2T2 RT2R3T2 RT2R4T2 RT2R5T2 RT2R6T2
RT02/T3 RT2R1T3 RT2R2T3 RT2R3T3 RT2R4T3 RT2R5T3 RT2R6T3
RT02/T4 RT2R1T4 RT2R2T4 RT2R3T4 RT2R4T4 RT2R5T4 RT2R6T4
etc

Thanks in advance

This gets me close, but it lists all 24 different region/Tier
combinations.

select [PType],
    (case when ([region] = 'R1' and [tier] = '1') then [NewRate] end) As R1,
    (case when ([region] = 'R2' and [tier] = '1') then [NewRate] end) As R2,
    (case when ([region] = 'R3' and [tier] = '1') then [NewRate] end) As R3,
    (case when ([region] = 'R4' and [tier] = '1') then [NewRate] end) As R4,
    (case when ([region] = 'R5' and [tier] = '1') then [NewRate] end) As R5,
    (case when ([region] = 'R6' and [tier] = '1') then [NewRate] end) As R6
from tblFY06FY07Compare
group by [region], [PType], [Tier], [NewRate]
order by [PType]



I get this...
RT01 45.90 NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL 47.20 NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL 52.50 NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL 57.05 NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL 45.90 NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL 52.20
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT02 43.70 NULL NULL NULL NULL NULL
RT02 NULL NULL NULL NULL NULL NULL
RT02 NULL NULL NULL NULL NULL NULL

GROUP BY *** Think "Aggragate"***

select [PType],
    SUM(case when ([region] = 'R1' and [tier] = '1') then [NewRate] ELSE 0 end) As R1,
    SUM(case when ([region] = 'R2' and [tier] = '1') then [NewRate] ELSE 0 end) As R2,
    SUM(case when ([region] = 'R3' and [tier] = '1') then [NewRate] ELSE 0 end) As R3,
    SUM(case when ([region] = 'R4' and [tier] = '1') then [NewRate] ELSE 0 end) As R4,
    SUM(case when ([region] = 'R5' and [tier] = '1') then [NewRate] ELSE 0 end) As R5,
    SUM(case when ([region] = 'R6' and [tier] = '1') then [NewRate] ELSE 0 end) As R6
from tblFY06FY07Compare
group by [PType]
order by [PType]


--Jeff Moden

Thanks Jeff,
You might have me on the right track, but
that doesn't quite do it. I want a separate row for each
Ptype/Tier pair. So, since there are 4 tiers, I'll have
4 rows for RT01, 4 rows for RT02, etc.

I need it to put values from more than one row into the
same row. But no values are added.

Not sure if I'm explaining it well enough.
That select statement sure can be a monster!

Looks like a job for Cursor man..... Let me know if you want to see a cursor that does it....

Sure. Whip it out.

I'll dig something up later this week.

try this running one part at a time. I did not have the time to fully verify it was correct but you should understand the logic you seek from this. Post questions if you don't.

-- Part 1
create table Rotate (Ptype char(4), Region char(2), Tier char(2), Value char(7))
go
-- Part 2

insert into rotate (ptype, region, tier, value) values ('RT01','R1','T1','RT1R1T1')
insert into rotate (ptype, region, tier, value) values ('RT01','R2','T1','RT1R2T1')
insert into rotate (ptype, region, tier, value) values ('RT01','R3','T1','RT1R3T1')
insert into rotate (ptype, region, tier, value) values ('RT01','R4','T1','RT1R4T1')
insert into rotate (ptype, region, tier, value) values ('RT01','R5','T1','RT1R5T1')

insert into rotate (ptype, region, tier, value) values ('RT01','R6','T1','RT1R6T1')
insert into rotate (ptype, region, tier, value) values ('RT01','R1','T2','RT1R1T2')
insert into rotate (ptype, region, tier, value) values ('RT01','R2','T2','RT1R2T2')
insert into rotate (ptype, region, tier, value) values ('RT01','R3','T2','RT1R3T2')
insert into rotate (ptype, region, tier, value) values ('RT01','R4','T2','RT1R4T2')

insert into rotate (ptype, region, tier, value) values ('RT01','R5','T2','RT1R5T2')
insert into rotate (ptype, region, tier, value) values ('RT01','R6','T2','RT1R6T2')

-- part 3

Declare @Ptype as char(4)
Declare @Region as char(2)
Declare @Tier as char(2)
Declare @Value as char(7)
Declare @Line as varchar(2000)
Declare @CurPtype as char(4)
Declare @CurTier as char(2)
Declare @Counter as int

DECLARE XCursor CURSOR FOR

SELECT ptype, tier, region, value from rotate order by ptype, tier, region, value
OPEN XCursor

FETCH NEXT FROM XCursor INTO @ptype,@tier,@region,@value
set @CurPtype = isnull(@ptype,'')
set @CurTier = isnull(@Tier,'')
set @Line = @ptype +' '+ @tier +' '
set @Counter = 1

WHILE @@FETCH_STATUS = 0
BEGIN

-- Print 'Counter: ' + convert(varchar(4),@counter)
-- Print 'Detail: ' + @ptype + ' ' + @region + ' ' + @tier + ' ' + @value
   if @CurTier = @Tier --@CurPtype = @ptype and
      Begin
         set @Line = ltrim(rtrim(@Line)) + ' ' + @value
      End
   Else
      Begin
         print @line
         set @Line = @ptype +' '+ @tier +' '+ @value
         set @CurPtype = @ptype
         set @CurTier = @Tier
      End

      set @Counter = @Counter + 1

FETCH NEXT FROM XCursor INTO @ptype,@tier,@region,@value

END
print @line

CLOSE XCursor
DEALLOCATE XCursor
go

-- --------------------------------
-- --------------------------------
-- --------------------------------

How hard ca it be to include one more column in the grouping?

select [PType],[Tier],
    SUM(case when [region] = 'R1' then [NewRate] ELSE 0 end) As R1,
    SUM(case when [region] = 'R2' then [NewRate] ELSE 0 end) As R2,
    SUM(case when [region] = 'R3' then [NewRate] ELSE 0 end) As R3,
    SUM(case when [region] = 'R4' then [NewRate] ELSE 0 end) As R4,
    SUM(case when [region] = 'R5' then [NewRate] ELSE 0 end) As R5,
    SUM(case when [region] = 'R6' then [NewRate] ELSE 0 end) As R6
from tblFY06FY07Compare
group by [PType], [Tier]
order by [PType], [Tier]

Good point but depends on how you look at the question. In your example if the number of regions changes you have issues..... I assumed it was just an example and could have a changing number of regions. Both are good solutions depending on the true need of the application.