SQL Server Multiple Pivots

Question

I would like some help please with the following.

Here is my SQL Server 2014 table schema:

Date [DateTime]
Invoice1 [float]
Invoice2 [float]
Operator [nvarchar]
Client [nvarchar]

In my Operator column I have 2 values: Operator 1 and Operator 2.

I would like to achieve the following output (in columns) in a single query:

Date
Invoice 1 of Operator 1
Invoice 1 of Operator 2
Invoice 2 of Operator 1
Invoice 2 of Operator 2
Count of Clients

I tried the following query but it returns an error: Aggregates on the right side of an APPLY cannot reference columns from the left side

SELECT *
FROM
(
 SELECT
 [Date],
 CASE
 when col = 'Invoice 1' then col + ' of ' + [Operator]
 when col = 'Invoice 2' then col + ' of ' + [Operator]
 when col = 'Clients' then col
 END AS pivot_col,
 CASE
 when col = 'Invoice 1' then SUM(value)
 when col = 'Invoice 2' then SUM(value)
 when col = 'Clients' then value
 END AS myvalues
FROM [table]
CROSS APPLY
(
VALUES
 ([Invoice1], 'Invoice 1'),
 ([Invoice2], 'Invoice 2'),
 (COUNT([Client]), 'Clients')
) x (value, col)
GROUP BY
 [Date],
 col + ' of ' + [Operator],
 col
) AS [Source]
PIVOT
(
 SUM(myvalues)
 FOR pivot_col IN (
 [Invoice 1 of Operator 1],
 [Invoice 1 of Operator 2],
 [Invoice 2 of Operator 1],
 [Invoice 2 of Operator 2],
 [Clients]
 )
) AS [Pivot]

I assume the error is due to the fact that I am using COUNT([Client]) inside the CROSS APPLY VALUES block. However I am not able to solve the problem.

Any ideas please?

Thanks Reda

[EDIT]

Bonjour Julien,

As requested:

Sample DB Data:

 Date | Invoice1 | Invoice2 | Operator | Client | Entity
----------------------------------------------------------------
 JAN | 10 | 15 | Operator 1 | Client 1 | Entity A
 FEB | 20 | 25 | Operator 1 | Client 1 | Entity B
 JAN | 30 | 35 | Operator 1 | Client 2 | Entity C
 FEB | 40 | 45 | Operator 1 | Client 2 | Entity D
 JAN | 50 | 55 | Operator 2 | Client 1 | Entity E
 FEB | 70 | 75 | Operator 2 | Client 2 | Entity F
 JAN | 80 | 85 | Operator 2 | Client 1 | Entity G
 FEB | 90 | 95 | Operator 2 | Client 2 | Entity H

Desired output (please scroll to the right):

 Date | Sum of Invoice 1 for Operator 1 | Sum of Invoice 2 for Operator 1 | Sum of Invoice 1 for Operator 2 | Sum of Invoice 2 for Operator 2 | Count of Distinct Clients | Count of Distinct Entities
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 JAN | 40 | 50 | 130 | 140 | 2 | 4
 FEB | 60 | 70 | 160 | 170 | 2 | 4

• Julien Vavasseur: can you add sample rows and expected output?
• Reda: Hi Julien, Please see edited question. Thanks!
• Julien Vavasseur: is date a datetime (and real date) or a string with JAN, FEB ?
• Reda: Date in Sample Data and Expected Output is real DateTime (full format) and shall remain as such. I wrote it in short month format for display purposes.
• Reda: Julien, I am sorry the expected output is wrong. I will rewrite it.

Answer 1

This works although I am not sure how to handle the date (sample is not a date):

Select DATENAME(month, [Date]) as 'date'
 , SUM(Case When Operator = 'Operator 1' then Invoice1 end) as 'Sum of Invoice 1 for Operator 1'
 , SUM(Case When Operator = 'Operator 1' then Invoice2 end) as 'Sum of Invoice 2 for Operator 1'
 , SUM(Case When Operator = 'Operator 2' then Invoice1 end) as 'Sum of Invoice 1 for Operator 2'
 , SUM(Case When Operator = 'Operator 2' then Invoice2 end) as 'Sum of Invoice 2 for Operator 2'
 , COUNT(distinct Client) as 'Count of Distinct Clients'
 , COUNT(distinct Entity) as 'Count of Distinct Entities'
From @data 
Group By [date];

Output:

date | Sum of Invoice 1 for Operator 1 | Sum of Invoice 2 for Operator 1 | Sum of Invoice 1 for Operator 2 | Sum of Invoice 2 for Operator 2 | Count of Distinct Clients | Count of Distinct Entities
January | 40 | 50 | 130 | 140 | 2 | 4
February | 60 | 70 | 160 | 170 | 2 | 4

Your data (replaced by real dates):

Declare @data TABLE ([Date] datetime, Invoice1 int, Invoice2 int, Operator varchar(10), Client varchar(8), Entity varchar(10));
INSERT INTO @data(Date, Invoice1, Invoice2, Operator, Client, Entity)
VALUES
 ('20150101', 10, 15, 'Operator 1', 'Client 1', 'Entity A'),
 ('20150201', 20, 25, 'Operator 1', 'Client 1', 'Entity B'),
 ('20150101', 30, 35, 'Operator 1', 'Client 2', 'Entity C'),
 ('20150201', 40, 45, 'Operator 1', 'Client 2', 'Entity D'),
 ('20150101', 50, 55, 'Operator 2', 'Client 1', 'Entity E'),
 ('20150201', 70, 75, 'Operator 2', 'Client 2', 'Entity F'),
 ('20150101', 80, 85, 'Operator 2', 'Client 1', 'Entity G'),
 ('20150201', 90, 95, 'Operator 2', 'Client 2', 'Entity H')
;

• Reda: Pourquoi compliquer quand on peut faire simple ! Thank you Julien, I went the complicated way of trying to pivot/unpivot but your solution is so simple.