0

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?

  • 1
    This is called "pivoting" and there are many ways to do it. Is the number of resulting columns always the same or are they dynamic? – Lamak Jan 4 '18 at 18:33
  • i am almost confident it would be the same but, lets make it dynamic. i have a status table, and each record should be represented as a column – highboi Jan 4 '18 at 18:36
3

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;
  • category isnt static, but with a few modifications, i got it working using your code. thanks for the help. didnt know what it was called either so thanks for that too =) – highboi Jan 4 '18 at 18:47
  • @highboi yeah, I figured it wasn't, but your sample data used static data for it, so I went with that – Lamak Jan 4 '18 at 18:48
2

Although @Lamak answer is a good solution I'd suggest another approach using CASE statement.

SELECT 'IT Queue' as Category,
       COUNT(CASE WHEN dbo.Status.Status = 'Complete' THEN 1 END AS Complete,
       COUNT(CASE WHEN dbo.Status.Status = 'Current' THEN 1 END AS Current,
       COUNT(CASE WHEN dbo.Status.Status = 'Hold' THEN 1 END AS Hold,
       COUNT(CASE WHEN dbo.Status.Status = 'In-Progress' THEN 1 END AS In-Progress,
       COUNT(CASE WHEN dbo.Status.Status = 'Killed' THEN 1 END AS Killed,
       COUNT(CASE WHEN dbo.Status.Status = 'New' THEN 1 END AS New,
       COUNT(CASE WHEN dbo.Status.Status = 'NTD' THEN 1 END AS NTD,
       COUNT(CASE WHEN dbo.Status.Status = 'Testing' THEN 1 END AS Testing
FROM   dbo.Status
INNER  JOIN dbo.CactusQueue_Task 
ON     dbo.Status.Id = dbo.CactusQueue_Task.StatusId
  • but...but..."i am almost confident it would be the same but, lets make it dynamic" – Lamak Jan 4 '18 at 18:55
  • @Lamak I was just writing it when your answer show up!!! – McNets Jan 4 '18 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.