Take the 2-minute tour ×

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:

(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.

share|improve this question
Please show sample data and desired results. It will often be easier to formulate a new query than to reverse engineer the query you have that doesn't do what you want. –  Aaron Bertrand Jan 9 '13 at 17:00
add comment

1 Answer

up vote 1 down vote accepted

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")

 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)

share|improve this answer
Thats great Hogan thanks for that, looks good. –  gallie Jan 9 '13 at 17:19
@gallie - yw, please upvote my answer too :) –  Hogan Jan 9 '13 at 17:21
Sorry, I tried but i'm only new to this game and apparently need to have built up a reputation before I can do that. –  gallie Jan 9 '13 at 17:29
add comment

Your Answer


