How to achieve below scenario with sql

user_14686595 0
Viewed 51

The table structure

ID      Name    Cost
-------------------------
1       Peter   10
1       Peter   20
1       Peter   30
2       Lily    10
2       Lily    20
2       Lily    30
-------------------------

and I will like to achieve something as below for reporting

ID      Name    Cost
-------------------------
1       Peter   10
                20
                30
2       Lily    10
                20
                30
-------------------------

so what I have try is using left join but instead of giving me a results like above, it returns like this, exactly the same with the table structure.

ID      Name    Cost     No Column Name
--------------------------------------
1       Peter   10
1       Peter   20
1       Peter   30
2       Lily    10
2       Lily    20
2       Lily    30
--------------------------------------
mysql sql
1 Answers

More of an expanded comment than an answer. Not properly tested or sure is the best method

select 
case q.R when 1 then cast(q.id as nvarchar) else '' end as _id,
case q.r when 1 then q.nm else '' end as _nm,
q.cost
from
(
select 
p.id,p.nm,p.cost,ROW_NUMBER() over (partition by p.id order by p.cost) as R
from tpeople as p
) as q
order by q.id
user_54147980
Related