How can I reformat a query result from 2 to 5 cols. Col1 'time' has up to 4 occurrences & Col2 'names' needs to be listed adjacent to correct time

Question

I have a query that returns 2 columns, 1 being 'time' which is not unique and may have up to 4 occurrences. The 2nd column is a list of 'names'. If need be I can easily add a third field which is unique to this data called RegId.

I want to modify the results of the query in such a way that I reduce the time column down to one instance (GROUP BY), but I want to show all names horizontally across in a table format similar to below.

'Start Time'  'Player 1'   'Player 2'   'Player 3'  'Player 4'

So far I have unsuccessfully tried to use the PIVOT command but may just be missing a trick on that one. Is there a straight forward command I should be using?

The closest closest I've gotten to it is as follows:

DECLARE @Registered TABLE
(CompetitionName VARCHAR(50),
CompetitionDate  Date,
StartTime TIME,
PlayersName  Varchar(60)
)
INSERT INTO @Registered
SELECT MAX(c.CompetitionName) AS 'Competition Name', MAX(c.[Date]) AS 'Competition Date', 
CONVERT(VARCHAR, r.PlayersStartTime, 108) AS 'Start Time', 
CASE WHEN m.MemberId IS NOT NULL THEN (m.Surname + ', ' + m.FirstName) ELSE (nm.Surname + ', '+ nm.Firstname) END AS PlayersName
FROM dbo.Competitions c
LEFT JOIN [dbo].[Registration] r ON c.[CompetitionId] = r.[CompetitionId]
LEFT JOIN dbo.Members m ON r.MemberId = m.MemberId
LEFT JOIN dbo.NonMembers nm ON r.NonMemberId = nm.NonMemberId
WHERE Date = '2013-01-04' AND c.CompetitionId = 10
GROUP BY r.PlayersStartTime, m.MemberId, m.FirstName, m.Surname, nm.FirstName, nm.Surname
----
SELECT [@Registered].StartTime, [@Registered].PlayersName AS 'Player 1', [@Registered].PlayersName AS 'Player 2',
[@Registered].PlayersName AS 'Player 3',[@Registered].PlayersName AS 'Player 4'
FROM @Registered

Any ideas would be very gratefully appreciated.

Answer 1

Here is a trick to get a comma separated list -- I bet this is what you want:

It will look like this:

StartTime    PlayerList
5:00         Sam, David, Joe
6:00         Lisa, Bart, Victor

To do that use a query like this (the XML "trick")

SELECT DISTINCT Main.StartTime, 
 STUFF((SELECT ', ' + PlayersName
  FROM [@Registered] Inner
  WHERE Inner.StartTime = Main.StartTime 
  FOR XML PATH ('')),1,2,'') AS PlayerList
FROM [@Registered] Main

Note, I did not test so it might have a typo (but I doubt it)