MSSQL Display Vertical Data in Columns

Question

Okay people, I have half solved my problem and I believe I need help solving the other half. I was able to stuff several rows of data from one column (TEAMS) and concatenate into one column, however, it appears that I need to do the same thing to another column within the same data set.

Here is the current data set example:

FIRST | LAST      | YEAR  | ID  | TEAMS
-- -- -- -- -- -- -- -- -- -- -- -- -- 
Chris | Anderson  | 2015  | 176 | 05 White (B)
Chris | Anderson  | 2016  | 176 | 05 Royal (B)
Chris | Anderson  | 2017  | 176 | 05 Royal (B), '08 Gray (B)
Chris | Anderson  | 2018  | 176 | 05 Royal (B), 08 (B)

What I need is to have the year VALUE as the column name and the teams data as the VALUE, like this:

FIRST | LAST     | 2015         | 2016         | 2017
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
Chris | Anderson | 05 White (B) | 05 Royal (B) | 05 Royal (B), '08 Gray (B)

Here is my existing SQL statement that produces the current (first) data set.

SELECT DISTINCT TOP 100 PERCENT dbo.Coaches.FirstName, dbo.Coaches.LastName, dbo.Teams.clubYear, dbo.Coaches.CoachID,
STUFF ((   SELECT  ', '+ SUBSTRING(TeamName,14,len(TeamName)) + ' ('+LEFT(gender,1)+')'
         FROM     dbo.Teams ST
         WHERE    ST.CoachID = dbo.Coaches.CoachID AND ST.clubYear = dbo.Teams.clubYear
         ORDER BY clubYear, CoachID
         FOR XML PATH('')), 1, 1, '') AS Teams FROM    dbo.Coaches INNER JOIN
    dbo.Teams  ON dbo.Coaches.CoachID = dbo.Teams.CoachID
WHERE   dbo.teams.teamTypeID = 3
GROUP BY dbo.Coaches.FirstName, dbo.Coaches.LastName, dbo.Teams.clubYear, dbo.Teams.TeamName, dbo.Coaches.CoachID
ORDER BY dbo.Coaches.LastName, dbo.Coaches.FirstName, dbo.Teams.clubYear

Answer 1

If the current data set example is the result of your statement, this should be rather easy. I'll mock-up your query by creating a similar dataset within a declared table variable. Just replace the SELECT * FROM @tbl with your statement.

DECLARE @tbl TABLE ([FIRST] VARCHAR(100),[YEAR] INT,TEAMS VARCHAR(100));
INSERT INTO @tbl VALUES
 ('Chris',2015,'White (B)')
,('Chris',2016,'05 Royal (B)')
,('Chris',2017,'05 Royal (B), 08 Gray (B)');

--The query will use your dataset and send it into PIVOT

SELECT p.*
FROM
(
    SELECT * FROM @tbl
) t
PIVOT
(
    MAX(TEAMS) FOR [YEAR] IN([2015],[2016],[2017])
) p;

The result

FIRST   2015        2016            2017
Chris   White (B)   05 Royal (B)    05 Royal (B), 08 Gray (B)