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"