SQL Server Add extra columns in Select Query

Well I found a way to get most of the way there using PIVOT and PARTITION and a temporary table but it's not elegant and it doesn't exactly match your expected outputs ...

Firstly, the id in your expected results seems meaningless as you want to combine multiple ids into a single set of results. I've ignored it.

Secondly, I don't know of any way of presenting the results as you have (partial data on the next row).

So here goes...

Firstly I ranked all of the data by PName and filtered out only rankings 1 through 4 (I've put an 'A' in front of the rank because I want to use that column as a column name later)

select
     PName,
     'A' + cast(rn as varchar) as colname,
  MinAmount

from
    (select PName, MinAmount,
    row_number() over (partition by PName order by MinAmount)  as rn
      from test) rankings
where rn IN (1,2,3,4)

This gave me the following results...

PNAME 	COLNAME MINAMOUNT
aa 	A1 	2.3 
aa 	A2 	3.5 
bbb 	A1 	4.2 
bbb 	A2 	4.3 
bbb 	A3 	5.2 
bbb 	A4 	5.6 
cc 	A1 	2.8 
cc 	A2 	2.9 
cc 	A3 	3.1 
dd 	A1 	5.1 
ee 	A1 	7.3 
ee 	A2 	8.5 
ee 	A3 	9.2 

I then used PIVOT on that data to get the rankings A1 to A4 as columns, putting the results into a temporary table. The whole query becomes

with filtered as
(
 select
     PName,
     'A' + cast(rn as varchar) as colname,
  MinAmount

from
    (select PName, MinAmount,
    row_number() over (partition by PName order by MinAmount)  as rn
      from test) rankings
where rn IN (1,2,3,4)
)
select * 
into #test1
FROM (
    SELECT 
        PName,
        colname,
        MinAmount
    FROM filtered
) as s
PIVOT
(
    SUM(MinAmount)
    FOR [colname] IN (A1,A2,A3,A4)
) as apivot

This gave the following results - which might actually be all you need depending on how you intend to display the results (in which case no need for the temporary table)

PNAME	A1	A2	A3	A4
aa 	2.3 	3.5 	(null) (null) 
bbb 	4.2 	4.3 	5.2 	5.6 
cc 	2.8 	2.9 	3.1 	(null) 
dd 	5.1 	(null) (null) 	(null) 
ee 	7.3 	8.5 	9.2 	(null) 

I finished off with the following query to get the results fairly close to the expected results you posted

SELECT PNAME, A1 as VeryCheap, A3 as Moderate FROM #test1
UNION ALL
SELECT PNAME, A2, A4 FROM #test1 where A2 is not null
ORDER BY 1,2

Note the check for not null on the second query to filter out some of the "rubbish"