Extract rowdata to multiple fields

[ARUNCHENNAI]

Hi,
I have a table [T_Run] with fields [TeamName],[ID],[RunnerCategory]
Table is created with every runner's ID, teamname & runnercategory(ie, captain, runner2,runner3...)

Id	TeamName	RunnerCategory
255	abc	RUNNER4
260	abc	RUNNER3
354	abc	CAPTAIN
52	abc	RUNNER2

<tbody>
</tbody>

Now I want to create a table based on Team name with fields
[TeamName],[Captain],[Runner2],[Runner3],[Runner4].
I want corresponding runners ID under their respective field(ie, captain, runner2,runner3...)
Like below.

TeamName	Captain	Runner2	Runner3	Runner4
abc	354	52	260	255

<tbody>
</tbody>

Note: I'm working on excel with sql query
Thank you.

 

[ARUNCHENNAI]

Picked a clue from below link
https://social.technet.microsoft.co...ing-text-in-pivot-table-data-area?forum=excel

And got the answer.

TRANSFORM First(T_Runners.[EmpId]) AS FirstOfEmpId
SELECT T_Runners.[TeamName]
FROM T_Runners
GROUP BY T_Runners.[TeamName]
PIVOT T_Runners.[RunnerCategory];