My database looks like this:

Raw Table Data :

The Result returned :

from the query below :

SELECT ROW_NUMBER() OVER ( ORDER BY stokkodu ) AS RowNum,
       stokkodu,
       adi,
       beden,
       renk,
       kalan,
       afiyat
  FROM (SELECT stokkodu, adi, afiyat, renk, SUM(kalan) AS kalan, beden
          FROM [viabase].[dbo].[Q_KALANS]
         GROUP BY stokkodu, adi, afiyat, renk, beden) AS products

Now I need to get the sizes of the products grouped under separate color nodes to fetch from the DB to present to the client site as in the JSON Format like this :

How can I do that? Is it possible?

CodePudding user response：

Unfortunately, SQL Server does not have the JSON_AGG function, which would have made this easier. Instead we need to hack it with a combination of STRING_AGG to aggregate, STRING_ESCAPE to correctly escape the values, and JSON_QUERY to prevent double-escaping.

You also need to get the base adi value. I've tried to do that with REPLACE and TRIM but cannot test due to lack of proper sample data.

WITH L1 AS (
  SELECT
    adi = TRIM(REPLACE(REPLACE(qk.adi, qk.renk, ''), qk.beden, '')),
    qk.renk,
    qk.beden,
    SUM(qk.kalan) AS kalan
  FROM dbo.Q_KALANS qk
  GROUP BY
    qk.adi,
    qk.renk,
    qk.beden
),
L2 AS (
  SELECT
    qk.adi,
    qk.renk,
    json = '{'   STRING_AGG(CONCAT('"', STRING_ESCAPE(qk.beden, 'json'), '":', SUM(kalan)), ',')   '}'
  FROM L1 qk
  GROUP BY
    qk.adi,
    qk.renk
)
SELECT
    qk.adi,
    STOCK = JSON_QUERY('{'   STRING_AGG(CONCAT('"', STRING_ESCAPE(qk.renk, 'json'), '":', qk.json), ',')   '}')
FROM L2 qk
GROUP BY
    qk.adi
FOR JSON PATH;

db<>fiddle