How do you condense the values in a T-SQL pivot into a JSON?-CodePudding

I have a table in SQL Server of the form:

date	userId	sessionStartTime	SessionEndTime	nClicks
2020-01-01	0	9:00:00	9:15:10	4
2020-01-01	0	9:33:00	10:05:05	2
2020-01-01	2	9:05:20	9:25:55	9
...	...	...	...	...

I'm looking to PIVOT it by userId and date and get something that consolidates the other columns as a JSON array. The output is envisioned to look like:

date	userId	data
2020-01-01	0	`[{'sessionStartTime':9:00:00, 'SessionEndTime':9:15:10, 'nClicks':4}, {'sessionStartTime':9:33:00, 'SessionEndTime':10:05:05, 'nClicks':2}]`
2020-01-01	2	`[{'sessionStartTime':9:05:20, 'SessionEndTime':9:25:55, 'nClicks':9}]`
...	...	...

Any ideas on how you could achieve this in SQL (T-SQL)?

CodePudding user response：

You need to generate the JSON content for each distinct combination of [date] and [userId]:

Table:

SELECT *
INTO Data
FROM (VALUES
   ('2020-01-01', 0, '9:00:00', '9:15:10', 4),
   ('2020-01-01', 0, '9:33:00', '10:05:05', 2),
   ('2020-01-01', 2, '9:05:20', '9:25:55', 9)
) v ([date], [userId], [sessionStartTime], [SessionEndTime], [nClicks])

T-SQL:

SELECT DISTINCT [date], [userId], [data]
FROM Data d
OUTER APPLY (
   SELECT [sessionStartTime], [SessionEndTime], [nClicks]
   FROM Data 
   WHERE d.[date] = [date] AND d.[userId] = [userId]
   FOR JSON PATH
) j (data)

Result:

date	userId	data
2020-01-01	0	[{"sessionStartTime":"9:00:00","SessionEndTime":"9:15:10","nClicks":4},{"sessionStartTime":"9:33:00","SessionEndTime":"10:05:05","nClicks":2}]
2020-01-01	2	[{"sessionStartTime":"9:05:20","SessionEndTime":"9:25:55","nClicks":9}]

CodePudding user response：

Unfortunately, SQL Server does not support JSON_AGG, which would have simplified things.

@Zhorov's answer requires a self-join, but this can actually be done with a single scan of the table and with normal aggregation, using STRING_AGG:

SELECT
  [date],
  [userId],
  STRING_AGG(N'['   [data]   N']', N',')
FROM Data d
CROSS APPLY (
   SELECT [sessionStartTime], [SessionEndTime], [nClicks]
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j (data)
GROUP BY
  [date],
  [userId];

db<>fiddle