If i understand you well... you want to convert rows into single column.
Take a look at these articles:
TSQL – Concatenate Rows using FOR XML PATH() | Sql And Me[
^]
STUFF AND FOR XML PATH for String Concatenation :: SQL Server[
^]
You can also use
CTE[
^]:
DECLARE @Teams TABLE (TeamId int, TeamName nvarchar(200))
insert into @Teams(TeamId,TeamName)
values (1,'Package'),
(2,'Parametric'),
(3,'Scribing'),
(4,'Lifecycle')
;WITH CTE AS
(
SELECT 1 AS tid, CAST(TeamName AS VARCHAR(MAX)) AS ResultField
FROM @Teams
WHERE TeamId = 1
UNION ALL
SELECT tid+ 1, CAST(CONCAT(c.ResultField, ',', t.TeamName) AS VARCHAR(MAX)) AS ResultField
FROM CTE AS c INNER JOIN @Teams AS t ON c.tid +1 = t.TeamId
WHERE EXISTS(SELECT TeamId FROM @Teams WHERE TeamId = tid+1)
)
SELECT TOP(1) ResultField
FROM CTE
ORDER BY tid DESC