SQL Server Multiple Pivots 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
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')
;