You have expressed a requirement, but not asked a question. What would you like help with, exactly?

Please provide consumable DDL, sample data and desired results so that people can try to assist, assuming you are looking for T-SQL assistance.