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.