how can i add key:values form of element inside a table column in microsoft sql server-CodePudding

i want to crete a column which has a name answer and store the multiple answer in single row column like like for student name mihir answer is {"asn":"que1","res":34}
and get the res for performing query

give me answer for my question

CodePudding user response：

That's not a recommended approach to storing this information with SQL Server. SQL Server does it's best work with groups and sets, and when designing your schema you want to leverage that as best you can.

You may want to consider something like this:

DECLARE @Users TABLE (UserID INT IDENTITY, Name NVARCHAR(20));
DECLARE @Answers TABLE (AnswerID INT, QuestionID INT, AnswerText NVARCHAR(200));
DECLARE @Questions TABLE (QuestionID INT IDENTITY, QuestionText NVARCHAR(200));
DECLARE @QuestionAnswers TABLE (UserID INT, QuestionID INT, AnswerID INT);

INSERT INTO @Users (Name) VALUES
('Aarsh'),('Patrick');

INSERT INTO @Questions (QuestionText) VALUES 
('What is your favorite food?'),('Which beverage do you enjoy most?');

INSERT INTO @Answers (AnswerID, QuestionID, AnswerText) VALUES
(1, 1, 'Bread'),(2, 1, 'Meat'),(3, 1, 'Fish'),(4, 1, 'Vegatables'),
(1, 2, 'Water'),(2, 2, 'Milk'),(3, 2, 'Soda'),(4, 2, 'Beer');

INSERT INTO @QuestionAnswers (UserID, QuestionID, AnswerID) VALUES
(1,1,3),(1,2,3),
(2,1,2),(2,2,4);

This is, by the way, a great way to provide sample data and objects when asking questions. This can be easily reproduced by anyone really quickly.

Using this sample data and objects we can quickly see how easy it is to get particular data sets back:

All the data:

SELECT u.UserID, u.Name AS UserName, q.QuestionText, a.AnswerText
  FROM @users u
    INNER JOIN @QuestionAnswers qa
      ON u.UserID = qa.UserID
    INNER JOIN @Questions q
      ON qa.QuestionID = q.QuestionID
    INNER JOIN @Answers a
      ON qa.AnswerID = a.AnswerID
      AND qa.QuestionID = a.QuestionID

UserID  UserName    QuestionText                        AnswerText
------------------------------------------------------------------
2       Patrick     What is your favorite food?         Meat
1       Aarsh       What is your favorite food?         Fish
1       Aarsh       Which beverage do you enjoy most?   Soda
2       Patrick     Which beverage do you enjoy most?   Beer

Questions and their Answers:

SELECT q.QuestionText, a.AnswerText
  FROM @Questions q
    LEFT OUTER JOIN @Answers a
      ON q.QuestionID = a.QuestionID;

QuestionText                        AnswerText
----------------------------------------------
What is your favorite food?         Bread
What is your favorite food?         Meat
What is your favorite food?         Fish
What is your favorite food?         Vegatables
Which beverage do you enjoy most?   Water
Which beverage do you enjoy most?   Milk
Which beverage do you enjoy most?   Soda
Which beverage do you enjoy most?   Beer

Questions with their answers as columns:

SELECT p.QuestionID, p.QuestionText, [1] AS Answer1, [2] AS Answer2, [3] AS Answer3, [4] AS Answer4
  FROM (SELECT q.QuestionID, q.QuestionText, a.AnswerID, a.AnswerText FROM @Questions q
    LEFT OUTER JOIN @Answers a
      ON q.QuestionID = a.QuestionID) a
    PIVOT (
           MAX(AnswerText) FOR AnswerID IN ([1],[2],[3],[4])
          ) p;

QuestionID  QuestionText                        Answer1 Answer2 Answer3 Answer4
-------------------------------------------------------------------------------
1           What is your favorite food?         Bread   Meat    Fish    Vegatables
2           Which beverage do you enjoy most?   Water   Milk    Soda    Beer

Questions with a count of their answers:

SELECT p.QuestionID, p.QuestionText, [1] AS Answer1, [2] AS Answer2, [3] AS Answer3, [4] AS Answer4
  FROM (SELECT q.QuestionID, q.QuestionText, a.AnswerID FROM @Questions q
    LEFT OUTER JOIN @QuestionAnswers a
      ON q.QuestionID = a.QuestionID) a
    PIVOT (
           COUNT(AnswerID) FOR AnswerID IN ([1],[2],[3],[4])
          ) p

QuestionID  QuestionText                        Answer1 Answer2 Answer3 Answer4
-------------------------------------------------------------------------------
1           What is your favorite food?         0       1       1       0
2           Which beverage do you enjoy most?   0       0       1       1

This was a long way to say columns are not objects, tables are. You can consider each column a property of it's table, but that's as granular as you ought to go with SQL Server (or most RDBMS systems). Tables can have columns which are links to other tables (Foreign key relationships) that allow for sub-properties in a way, Consider:

DECLARE @Users TABLE (UserID INT IDENTITY, Name NVARCHAR(20), ContactID INT);
DECLARE @Contacts TABLE (ContactID INT IDENTITY, ContactType INT, IsPrimary BIT, Conatct NVARCHAR(100));
DECLARE @ContactType TABLE (ContactTypeID INT IDENTITY, ContactTypeName NVARCHAR(20));

You could consider this to be a Users object, with a contact property that has it's own sub-property of ContactTypeID and Name.

If you want to consider using unstructured objects like you're alluding to, then a NoSQL system (instead of a RDBMS system) might be a better option for you, but you really need to consider the pros and cons for each before you determine that.