SQL Server stored procedure / query
Budget: $10 – $30 USD
Hello, I'm using SQL Server 2019 in a C# project. I have 2 tables:
[Quizzes]
ID uniqueidentifier
CreationDate datetime
Author nvarchar(50)
[Questions]
ID uniqueidentifier
QuizID uniqueidentifier
Text nvarchar(MAX)
I could make a query like this to get the results (I need to list all quizzes with their questions) in a normal way:
SELECT * FROM Quizzes
LEFT JOIN Questions ON Quizzes.ID = Questions.QuizID
The problem is that I need to convert all questions to json array and return them as a new column (called 'questions' nvarchar(MAX)) in each quiz record. In other words, I would need a result set like this:
ID, CreationDate, Author, Questions
22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed, 2021-07-10, 'John', '[{"ID":"CE0B3372-DB67-4DB4-ABCE-BBFDB948CCCD","QuizID":"22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed","Text":"What color is the Sun?"},{"ID":"0D8DC6A2-ADBB-4951-9212-0F664E62D047","QuizID":"22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed","Text":"How much is 5+7?"}]'
To further clarify, the "Questions" column should contain (in JSON format) all questions for that quiz id. The number of rows of the query should equal the number of quizzes.
How can I do it with a single query? Of course I could do a cycle to build the json, but I don't want to do it because it would not be performance-wise.
Thanks!
[Quizzes]
ID uniqueidentifier
CreationDate datetime
Author nvarchar(50)
[Questions]
ID uniqueidentifier
QuizID uniqueidentifier
Text nvarchar(MAX)
I could make a query like this to get the results (I need to list all quizzes with their questions) in a normal way:
SELECT * FROM Quizzes
LEFT JOIN Questions ON Quizzes.ID = Questions.QuizID
The problem is that I need to convert all questions to json array and return them as a new column (called 'questions' nvarchar(MAX)) in each quiz record. In other words, I would need a result set like this:
ID, CreationDate, Author, Questions
22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed, 2021-07-10, 'John', '[{"ID":"CE0B3372-DB67-4DB4-ABCE-BBFDB948CCCD","QuizID":"22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed","Text":"What color is the Sun?"},{"ID":"0D8DC6A2-ADBB-4951-9212-0F664E62D047","QuizID":"22f947b8-0e45-4ec8-a7c2-aed0fb2f66ed","Text":"How much is 5+7?"}]'
To further clarify, the "Questions" column should contain (in JSON format) all questions for that quiz id. The number of rows of the query should equal the number of quizzes.
How can I do it with a single query? Of course I could do a cycle to build the json, but I don't want to do it because it would not be performance-wise.
Thanks!
Related categories:
SQL
MySQL
Database Programming
Microsoft SQL Server
T-SQL (Transact Structures Query Language)