Crosstab query get price of last date from table

I have three table:

Person

ProductGroup

Price of each ProductGroup for Person with date

I want below result that get price of last date of each ProductGroup

Help me please.

I get result by create table and then set cell by cell

But I want this in one query

For logic to select the latest Price for each ProductGroupID and PersonID combination, see Get top 1 row of each group. The general technique is to use the ROW_NUMBER() window function to number the rows within each group, and then filter for row-number = 1.

After you have that, it is just a matter of performing a PIVOT operation.

SELECT PVT.[GROUP C], PVT.[GROUP B], PVT.[GROUP A], PVT.FullName, PVT.PersonId
FROM (
    SELECT PG.ProductGroupName, PGP.Price, P.FullName, P.PersonId
    FROM (
        SELECT
            *,
            ROW_NUMBER()
                OVER(PARTITION BY ProductGroupID, PersonID ORDER BY PriceDate DESC)
                AS RowNum
        FROM ProductGroupPrice
    ) PGP
    JOIN ProductGroup PG
        ON PG.ProductGroupID = PGP.ProductGroupID
    JOIN person P
        ON P.PersonID = PGP.PersonID
    WHERE PGP.RowNum = 1
) Q
PIVOT (
    MAX(Q.Price)
    FOR Q.ProductGroupName IN ([GROUP C], [GROUP B], [GROUP A])
) PVT
ORDER BY PVT.PersonId

An alternative to using PIVOT is conditional aggregation, where you define select items like MAX(CASE WHEN <condition> THEN <value> END) AS <column>.

SELECT
    MAX(CASE WHEN PG.ProductGroupName = 'GROUP C' THEN PGP.Price END) AS [GROUP C],
    MAX(CASE WHEN PG.ProductGroupName = 'GROUP B' THEN PGP.Price END) AS [GROUP B],
    MAX(CASE WHEN PG.ProductGroupName = 'GROUP A' THEN PGP.Price END) AS [GROUP A],
    P.FullName,
    P.PersonId
FROM (
    SELECT
        *,
        ROW_NUMBER()
            OVER(PARTITION BY ProductGroupID, PersonID ORDER BY PriceDate DESC)
            AS RowNum
    FROM ProductGroupPrice
) PGP
JOIN ProductGroup PG
    ON PG.ProductGroupID = PGP.ProductGroupID
JOIN person P
    ON P.PersonID = PGP.PersonID
WHERE PGP.RowNum = 1
GROUP BY P.FullName, P.PersonId
ORDER BY P.PersonId

Intermediate results:

Final results:

See this db<>fiddle for a demo of both approaches.