How can use multiple aggregation in pivot query.

 Syntax for PIVOT.

PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

Below table we have Suppliercode and amount. We want change Row data in column means HP, CBS & GRN as column name also find total number of supplier code in table base of unique supplier.

Another output total amount base of unique supplier code.

ID	SupplierCode	Amount
1	HP	29
2	HP	30
3	CBS	40
4	CBS	50
5	HB	50

In below pivot query using multiple aggregation in pivot query for our output.

select * from cost

SELECT * FROM (

select CBS as CBS_Count,HB as HB_Count,HP as HP_Count from (

select count(SupplierCode)  as Code,SupplierCode  from cost

group by SupplierCode) as t

PIVOT (SUM(CODE) FOR SupplierCode IN([CBS],[HB],[HP])) AS PIB) T1,

(select * from

(

SELECT SupplierCode,SUM(Amount) as Total  from cost group by SupplierCode) as T2

pivot

(

SUM(Total) for SupplierCode in([CBS],[HB],[HP])) AS totalamount) T3

OUTPUT:

CBS_Count	HB_Count	HP_Count	CBS	HB	HP
2	1	2	90	50	59