Pivot One Column and Retain SUM,AVG,COUNT Columns

Let’s say I have a table PRODUCT like this.

ProductName    Amount
---------------------
Soap           1.10
Toothbrush     2.00
Towel          3.00
Soap           3.00

Then I get their sum, average and count using group by.

SELECT 
    [ProductName],
    SUM([Amount]) AS 'Sum', 
    COUNT(*) AS 'Count',
    AVG([Amount]) AS 'Avg'
FROM Product
GROUP BY [ProductIdName]

I have this result.

ProductName  Sum  Count  Avg
-----------------------------
Soap         4.10     2  2.05 
Toothbrush   2.00     1  2.00 
Towel        3.00     1  3.00

I want to pivot the productname column into something like this.

Type               Soap  Toothbrush  Towel
------------------------------------------
Products Sum       4.10        2.00   3.00 
Products Count        2           1      1
Products Average   2.05        2.00   3.00

I have used pivot to each aggregate then union all, which I think is too verbose and not efficient? So I’m thinking if there are other solutions for this.

    SELECT 
        'Products Sum' AS [Type],
        *
    FROM Product t
    PIVOT(
        SUM(Amount)     
        FOR [ProductName] IN (
            [Soap], 
            [Toothbrush], 
            [Towel]
        )
    ) AS pivot_table
    UNION ALL
    SELECT 
        'Products Count' AS [Type],
        *
    FROM Product t
    PIVOT(
        COUNT(Amount)       
        FOR [ProductName] IN (
            [Soap], 
            [Toothbrush], 
            [Towel]
        )
    ) AS pivot_table
    ...etc ( pivot AVG(Amount) )

Answer

You can achieve this by using both UNPIVOT and PIVOT.

SELECT 
        'Products ' + col as [Type], [Soap], [Toothbrush], [Towel]
from
(SELECT 
    ProductName,
    CONVERT(sql_variant, SUM([Amount])) AS 'Sum', 
    CONVERT(sql_variant, COUNT(*)) AS 'Count',
    CONVERT(sql_variant, AVG([Amount])) AS 'Avg'
FROM Product
GROUP BY ProductName
)t
unpivot
(
    val for col in ([sum], [count], [avg])
)upvt
pivot
(
    max(val) for ProductName in ([Soap], [Toothbrush], [Towel])
)pvt

Please see the demo here.