Aggregation of data on 2 columns using pivot and stuff

Question

Have been trying to achieve using Stuff and Pivot in the dynamic Query. I end up getting the unwanted cells either in the Row/column.

Input

Output

Answer 1

Use pivot two times.

CREATE TABLE #Stu (Student int, Quantity INT, Age INT, Price DECIMAL(10,2))
INSERT INTO #Stu SELECT 1, 1, 1, 2.5
INSERT INTO #Stu SELECT 2, 12, 1, 2
INSERT INTO #Stu SELECT 2, 2, 2, 1.5
INSERT INTO #Stu SELECT 1, 3, 3, 2.5
INSERT INTO #Stu SELECT 2, 3, 3, 1.5

DECLARE @Ages NVARCHAR(MAX)
DECLARE @AgesOnSelect NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SELECT 
    @Ages = COALESCE(@Ages + ', ','') + '[' + CAST(S.Age AS NVARCHAR(4)) + ']',
    @AgesOnSelect = COALESCE(@AgesOnSelect + ', ','') + 'ISNULL(CAST(Pivot1.[' + CAST(S.Age AS NVARCHAR(4)) +'] AS NVARCHAR(4))  + ''@'' + CAST(Pivot2.[' + CAST(S.Age AS NVARCHAR(4)) +'] AS NVARCHAR(4)), 0) AS ''Age ' + CAST(S.Age AS NVARCHAR(4)) +''''
FROM #Stu S 
GROUP BY S.Age

SET @Query = 
        'SELECT
            Pivot1.Student ,
            ' +  @AgesOnSelect  + '         
        FROM
        (
            SELECT  
                *                               
            FROM
            (
                SELECT 
                    Student ,
                    Quantity ,
                    Age 
                FROM 
                    #Stu
            ) AS A
            PIVOT
            (
                MIN(A.Quantity)
                FOR Age IN (' + @Ages + ')' + '
            ) AS P
        ) Pivot1 INNER JOIN 
        (
            SELECT  
                *
            FROM
            (
                SELECT 
                    Student ,        
                    Age ,
                    Price
                FROM 
                    #Stu
            ) AS A
            PIVOT
            (
                MIN(A.Price)
                FOR Age IN ([1],[2],[3])
            ) AS P 
        ) Pivot2 ON Pivot1.Student = Pivot2.Student'

-- SELECT @Query
EXEC sp_executesql @Query

Output:

Student     Age 1     Age 2     Age 3
----------- --------- --------- ---------
1           1@2.50    0         3@2.50
2           12@2.00   2@1.50    3@1.50