String aggregation with two columns using JSON in SQL Server 2016

Question

My result contains data like:

CustomerId | Rating (Star) | RatingCount
  1        |   1           |   20
  1        |   2           |   15
  1        |   3           |   4
  1        |   4           |   30
  1        |   5           |   36
  2        |   1           |   5
  2        |   2           |   4
  2        |   5           |   10

But I would like to transform the result as:

CustomerId  | CustomerRatings
   1        | { "1": 20, "2": 15, "3": 4 , "4": 30, "5": 36 }
   2        | { "1": 5, "2": 4, "5": 10 }

I have used the FOR JSON Path function to transfrom the data as

@JSONString = {"_":"1","__":20},{"_":"2","__":15},{"_":"3","__":4},{"_":"4","__":30},{"_":"5","__":36}

Then used the replace method to get the actual result.

SELECT REPLACE(REPLACE(REPLACE(@JSONString,'"_":',''),',"__":',':'),'},{',',')

{"1":20,"2":15,"3":4,"4":30,"5":36}

Is there a better way to achieve that? I started using SQL server 2016 last couple of days.

Salman Arshad · Accepted Answer · 2019-11-27 14:59:16Z

1

Assuming that rating is always between 1 and 5:

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

Use INCLUDE_NULL_VALUES option to include null values, or COALESCE(..., 0) to convert null to zeros.

Demo on db<>fiddle

edited Nov 27, 2019 at 14:59

answered Nov 27, 2019 at 14:52

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arooran Over a year ago

Can you please look at this fiddle dbfiddle.uk/… I would like to get the result like {"APV": {"1":1,"3":3,"4":24,"5":95}, "VN":{"1":3,"5":25} }

Arooran Over a year ago

Yes your a correct,This is for returning data, not storing data.

Collectives™ on Stack Overflow

String aggregation with two columns using JSON in SQL Server 2016

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related