Dynamic Pivot

[DevStudent]

Hi,
I can't find anything decent online to help me out in finding how I could convert my following static sql pivot script into a dynamic pivot :

with cte as (SELECT ppl.name,
ppl.surname,
ppl.phone,
pos.positionName,
com.companyName,
ts.tagName,
'TagName' + CONVERT(varchar(250),ROW_NUMBER() over (partition by ppl.peopleRefId order by ts.tagName)) as Rn
from people ppl
JOIN companies com
ON ppl.companyRefId=com.companyRefId
JOIN tagLinks tl
on ppl.peopleRefId=tl.personRefId
join tags ts
on ts.tagRefId=tl.tagRefId
join positions pos
on pos.positionRefId=ppl.positionRefId)

(select * from cte PIVOT (max(TagName) FOR Rn IN ([TagName1],[TagName2],[TagName3])) pvt)

Any help will be much appreciated. I have already looked at a few examples online but I havent been able to find anything that contains joins