How do i merge multiple records into a single record I have a select query:

SELECT
  'IT Queue' as Category,
  dbo.Status.Status,
  COUNT(*) AS Count
FROM dbo.Status
  INNER JOIN dbo.CactusQueue_Task ON dbo.Status.Id = dbo.CactusQueue_Task.StatusId
GROUP BY dbo.Status.Status


and it outputs the following:

+-----------+----------------------------+
| Category  | Status              | Count | 
+-----------+-----------------------------+ 
| IT Queue  | Complete            | 1576  | 
| IT Queue  | Current             | 2     | 
| IT Queue  | Hold                | 7     | 
| IT Queue  | In-Progress         | 56    | 
| IT Queue  | Killed              | 1     | 
| IT Queue  | New                 | 71    | 
| IT Queue  | NTD                 | 1     | 
| IT Queue  | Testing             | 2     | 
+-----------+-----------------------------+


would like to modify the query to select a single record like below:

+-----------+----------+---------+------+-------------+--------+-----+-----+---------+
| Category  | Complete | Current | Hold | In-Progress | Killed | New | NTD | Testing |
+-----------+----------+---------+------+-------------+--------+-----+-----+---------+
| IT Queue  | 1576     |       2 |    7 |          56 |      1 |  71 |   1 |       2 |
+-----------+----------+---------+------+-------------+--------+-----+-----+---------+


How would I go about doing so?
You can use PIVOT and dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
DECLARE @category NVARCHAR(20) = N'IT Queue';

SELECT @cols = @cols + STUFF((  SELECT ',' + QUOTENAME([Status])
                               FROM dbo.[Status]
                               GROUP BY [Status]
                               FOR XML PATH('')), 1, 1, '');

SET @sql = N' SELECT *
              FROM (SELECT  ''' + @category + ''' Category,
                            s.[Status],
                            q.StatusId
                    FROM dbo.[Status] s
                    INNER JOIN dbo.CactusQueue_Task q
                        ON s.Id = q.StatusId) AS d
  PIVOT (COUNT(StatusId) FOR [Status] IN (' + @cols + ')) AS p;';

EXEC sp_executesql @sql;