By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t(CustomerId int, Rating int, RatingCount int);
INSERT INTO t(CustomerId, Rating, RatingCount) VALUES
(1, 1, 20),
(1, 2, 15),
(1, 3, 4),
(1, 4, 30),
(1, 5, 36),
(2, 1, 5),
(2, 2, 4),
(2, 5, 10);
8 rows affected
SELECT CustomerId, (
SELECT MIN(CASE WHEN Rating = 1 THEN RatingCount END) AS [1]
, MIN(CASE WHEN Rating = 2 THEN RatingCount END) AS [2]
, MIN(CASE WHEN Rating = 3 THEN RatingCount END) AS [3]
, MIN(CASE WHEN Rating = 4 THEN RatingCount END) AS [4]
, MIN(CASE WHEN Rating = 5 THEN RatingCount END) AS [5]
FROM t AS x
WHERE x.CustomerId = t.CustomerId
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)
FROM t
GROUP BY CustomerId
| CustomerId | (No column name) |
|---|---|
| 1 | {"1":20,"2":15,"3":4,"4":30,"5":36} |
| 2 | {"1":5,"2":4,"5":10} |
Warning: Null value is eliminated by an aggregate or other SET operation.