Создание хранимой процедуры для объединения строк

Наличие таблицы транзакций со следующими строками:

Id    UserId   PlatformId    TransactionTypeId
-------------------------------------------------
 0       1          3                1
 1       1          1                2
 2       2          3                2
 3       3          2                1
 4       2          3                1

Как написать хранимую процедуру, которая может агрегировать строки в новую таблицу со следующим форматом?

Id    UserId   Platforms          TransactionTypeId
-------------------------------------------------
 0      1      {"p3":1,"p1":1}    {"t1":1,"t2":1}
 1      2      {"p3":2}           {"t2":1,"t1":1}
 3      3      {"p2":1}           {"t1":1}

Таким образом, строки выгравированы пользователем, подсчитывают каждую платформу / тип транзакции и сохраняются как строка ключа / значения json.

Ссылка: Мой предыдущий связанный вопрос

sql-server,azure,stored-procedures,aggregate,

1

Ответов: 2

1 принят

Вы можете использовать GROUP BYи FOR JSON:

SELECT MIN(ID) AS ID, UserId, MIN(sub.x) AS Platforms, MIN(sub2.x) AS Transactions
FROM tab t
OUTER APPLY (SELECT CONCAT('p', platformId) AS platform, cnt 
            FROM (SELECT PlatformId, COUNT(*) AS cnt
                  FROM tab t2 WHERE t2.UserId = t.UserId  
                  GROUP BY PlatformId) s
              FOR JSON AUTO) sub(x)
OUTER APPLY (SELECT CONCAT('t', TransactiontypeId) AS Transactions, cnt 
            FROM (SELECT TransactiontypeId, COUNT(*) AS cnt
                  FROM tab t2 WHERE t2.UserId = t.UserId  
                  GROUP BY TransactiontypeId) s
              FOR JSON AUTO) sub2(x)
GROUP BY UserId;

Демоверсия DBFiddle

Результат немного отличается (массив ключевого значения), но, пожалуйста, рассматривайте его как отправную точку.

1

Ваш образец JSON на самом деле не json, но так как вы этого хотите:

SELECT u.UserId, plt.pValue, ttyp.ttValue
FROM Users AS [u]
CROSS APPLY (
SELECT '{'+STUFF( (SELECT ',"'+pn.pName+'":'+LTRIM(STR(pn.pCount))
    FROM (SELECT p.Name AS pName, COUNT(*) AS pCount 
        FROM transactions t
        left JOIN Platforms p ON p.PlatformID = t.PlatformId
        WHERE t.UserId = u.UserId
        GROUP BY p.PlatformId, p.Name
        ) pn
    FOR XML PATH('')),1,1,'')+'}'
    ) plt(pValue)
CROSS APPLY (
SELECT '{'+STUFF( (SELECT ',"'+tty.ttName+'":'+LTRIM(STR(tty.ttCount))
    FROM (SELECT tt.Name AS ttName, COUNT(*) AS ttCount 
        FROM transactions t
        left JOIN dbo.TransactionType tt ON tt.TransactionTypeId = t.TransactionTypeID
        WHERE t.UserId = u.UserId
        GROUP BY tt.TransactionTypeId, tt.Name
        ) tty
    FOR XML PATH('')),1,1,'')+'}'
    ) ttyp(ttValue)
WHERE EXISTS (SELECT * FROM transactions t WHERE u.UserId = t.UserId)
ORDER BY UserId;

Образец DBFiddle

SQL-сервер, лазурь, хранимая-процедура, совокупность,
Похожие вопросы
Яндекс.Метрика