How to transform SQL Server query output in a certain way?

Question

I have a query producing the following output:

HostName    ServiceName Name        count
NULL        NULL        Received    24
canws82     MyBJE       Allocated   4
canws82     MyBJE       Scheduled   3
canws82     MyBJE       Running     3
NCBJE1      MyBJE       Running     3
NCBJE3      MyBJE       Running     3
NCBJE9      MyBJE       Allocated   37
NCBJE9      MyBJE       Scheduled   3
NCBJE9      MyBJE       Running     3

Now I am stuck trying to figure out how to transform this output into

HostName    ServiceName Received    Allocated   Scheduled   Running
NULL        NULL        24          0           0           0
canws82     MyBJE       0           4           3           3
NCBJE1      MyBJE       0           0           0           3
NCBJE3      MyBJE       0           0           0           3
NCBJE9      MyBJE       0           37          3           3

I do not even know the name of this transformation, so if anyone does please feel free to edit the title (and remove this sentence :-)).

Any ideas?

P.S.

I am using SQL Server 2008R2.

EDIT

Thanks to Monty Wild I know now it is PIVOT I am after. So, I came up with the following code:

DECLARE @Tmp TABLE (HostName NVARCHAR(32), ServiceName NVARCHAR(32), Name NVARCHAR(32), count INT)
INSERT INTO @Tmp (HostName, ServiceName, Name, count) VALUES
(NULL        ,NULL        ,'Received'    ,24),
('canws82'     ,'MyBJE'       ,'Allocated'   ,4),
('canws82'     ,'MyBJE'       ,'Scheduled'   ,3),
('canws82'     ,'MyBJE'       ,'Running'     ,3),
('NCBJE1'      ,'MyBJE'       ,'Running'     ,3),
('NCBJE3'      ,'MyBJE'       ,'Running'     ,3),
('NCBJE9'      ,'MyBJE'       ,'Allocated'   ,37),
('NCBJE9'      ,'MyBJE'       ,'Scheduled'   ,3),
('NCBJE9'      ,'MyBJE'       ,'Running'     ,3)

;WITH pivoted AS ( 
    SELECT HostName, ServiceName, Received, Allocated, Scheduled, Running FROM @Tmp
    PIVOT (SUM(count) FOR Name IN (Received, Allocated, Scheduled, Running)) as pvt
)
SELECT HostName, ServiceName, SUM(ISNULL(Received, 0)) Received, SUM(ISNULL(Allocated, 0)) Allocated, SUM(ISNULL(Scheduled, 0)) Scheduled, SUM(ISNULL(Running, 0)) Running
FROM pivoted
GROUP BY HostName, ServiceName

And it does produce the desired result. However, I have a strong feeling that my code is not the best solution. I am sure there is a better way.

EDIT 2

My real SQL code is a bit different. Here it is:

;WITH raw AS (
    SELECT AllocatedAgentHostName, AllocatedAgentServiceName, Status, COUNT(1) count
    FROM BackgroundJobWork bjw
    WHERE Status < 100
    GROUP BY AllocatedAgentHostName,AllocatedAgentServiceName,Status
), data AS (
    SELECT raw.*, wsn.Name
    FROM raw
    JOIN WorkStatusName wsn ON raw.Status = wsn.Id
), pivoted AS ( 
    SELECT AllocatedAgentHostName, AllocatedAgentServiceName, Received, Allocated, Scheduled, Running FROM data
    PIVOT (SUM(count) FOR Name IN (Received, Allocated, Scheduled, Running)) as pvt
)
SELECT * FROM pivoted
ORDER BY AllocatedAgentHostName, AllocatedAgentServiceName

Running this query produces the following output:

AllocatedAgentHostName  AllocatedAgentServiceName Received  Allocated Scheduled Running
NULL                    NULL                      24        NULL      NULL      NULL
canws82                 MyBJE                     NULL      4         NULL      NULL
canws82                 MyBJE                     NULL      NULL      3         NULL
canws82                 MyBJE                     NULL      NULL      NULL      3
NCBJE1                  MyBJE                     NULL      NULL      NULL      3
NCBJE3                  MyBJE                     NULL      NULL      NULL      3
NCBJE9                  MyBJE                     NULL      37        NULL      NULL
NCBJE9                  MyBJE                     NULL      NULL      3         NULL
NCBJE9                  MyBJE                     NULL      NULL      NULL      3

I have no idea why does it produce the staircase effect, which does not occur in the contrived example with the @Tmp table.

EDIT 3

OK, I found the cause for the staircase (even though I cannot explain why it is the cause). My real code data has an extra column - Status, which is not selected into pivoted. Somehow this causes the staircase.

Monty Wild · Accepted Answer · 2014-03-13 02:39:59Z

1

The term you are looking for is "Pivot". Have a look at the TechNet Using PIVOT and UNPIVOT page, and if you still can't understand it, someone may be able to help further.

answered Mar 13, 2014 at 2:39

Monty Wild

3,9991 gold badge26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mark Over a year ago

+1 - thanks for the hint. I did come up with a solution, but it is an awkward one. I do not like it very much.

thepirat000 Over a year ago

T-SQL PIVOT is always awkard

Collectives™ on Stack Overflow

How to transform SQL Server query output in a certain way?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related