1

I have a table Checklist with the following columns

ChkId, Q1_ID, Comment1, Checked1, Q2_ID, Comment2, Checked2, ... Q200_ID...

I have a table Questions with the following columns

Q_ID, Prompt

How do I replace Checklist.Q#_ID with the matching Questions.Prompt without joining them up to 200 times? Is there an elegant solution?

Improve this question
6
  • Your best bet is to probably pivot the Checklist table inside a CTE or possibly temp table and then join onto the CTE Commented Oct 24, 2017 at 8:22
  • 1
    Have a look at dynamic queries. Commented Oct 24, 2017 at 8:31
  • 6
    This is a horrible design. You should normalize your data model, then the query is a simple join. Commented Oct 24, 2017 at 12:04
  • 1
    Rollback;Rollback;Rollback; Commented Feb 14, 2018 at 20:30
  • 1
    Please don't vandalize your posts to have them deleted. Commented Feb 14, 2018 at 20:30

2 Answers 2

Reset to default
5

Take a look at my answer here to understand why your original design is a very bad idea from a relational database point of view.

Basically, you have "squashed" two entities into one! Your scenario is very similar to that question! You need to normalise your data - this will greatly assist your query formulation and speed!

It appears that there are two entities here (IMHO) a checklist entity and a question entity.

Checklists are identified by a checklist_id (chk_id) and questions are uniquely identifed by a checklist_id and a question_id. Perhaps "quiz" might be a better name? You could have history quizzes, sports quizzes &c. with different chk_id's.

So, in order to tackle the underlying problem, you should create a question table (see SQLFiddle here):

CREATE TABLE question ( chk_id INTEGER NOT NULL, q_id INTEGER NOT NULL, q_comment VARCHAR (255) NOT NULL, q_checked BIT NOT NULL, CONSTRAINT question_pk PRIMARY KEY (chk_id, q_id) );

You could implement my strategy in VIEWs if you can't change the underlying table structure - that will be a bit clumsy, but still better than your current scenario!

Sample checklist table:

A sample of the first 3 questions extracted from your checklist table and sample data:

CREATE TABLE checklist -- you have this already! ( chk_id INTEGER PRIMARY KEY, q1_id INTEGER NOT NULL, q1_comment VARCHAR (255) NOT NULL, -- or whatever size suits. q1_checked BIT NOT NULL, q2_id INTEGER NOT NULL, q2_comment VARCHAR (255), q2_checked BIT NOT NULL, q3_id INTEGER NOT NULL, q3_comment VARCHAR (255), q3_checked BIT NOT NULL -- &c. 197 times. ); INSERT INTO checklist VALUES (233, 1, 'Good question!', 1, 2, 'Crap', 0, 3, 'Better', 1), (234, 1, 'An OK question', 0, 2, 'Bleuch...', 1, 3, 'Brilliant!!', 0);

If the "comment" field is the question itself - i.e. "prompt", substitute text of your choice - my comments are opinions about the question... not really sure. Easily modifiable!

You will then need to construct a series of 200 INSERTs like this:

INSERT INTO question SELECT chk_id, q1_id, q1_comment, q1_checked FROM checklist;

You will have to do this 200 times (q2... q200). Obviously, this will require some work (either manual typing or scripting) to get your data out of your original entity, but it will be worth it in the long run!

Here are the three question entries (by two checklists - history or sport):

SELECT * FROM question;

Result:

chk_id q_id q_comment q_checked 233 1 Good question! true 233 2 Crap false 233 3 Better true 234 1 An OK question false 234 2 Bleuch... true 234 3 Brilliant!! false

Your tables will be much more performant and much easier to manipulate!

Improve this answer
0

In the example below I've assumed that the comment field is what you're referring to as prompt.

Build some test data;

IF OBJECT_ID('tempdb..#TestingTable') IS NOT NULL DROP TABLE #TestingTable CREATE TABLE #TestingTable ( ChkID int ,Q1_ID int ,Comment1 varchar(20) ,Q2_ID int ,Comment2 varchar(20) ,Q3_ID int ,Comment3 varchar(20) ) INSERT INTO #TestingTable (ChkID, Q1_ID, Comment1, Q2_ID, Comment2, Q3_ID, Comment3) VALUES (1,11,'Row1 Comment1', 12, 'Row1 Comment2', 13, 'Row1 Comment3') ,(2,21,'Row2 Comment1', 22, 'Row2 Comment2', 23, 'Row2 Comment3') ,(3,31,'Row3 Comment1', 32, 'Row3 Comment2', 33, 'Row3 Comment3') ,(4,41,'Row4 Comment1', 42, 'Row4 Comment2', 43, 'Row4 Comment3')

Which gives you this data set;

ChkID Q1_ID Comment1 Q2_ID Comment2 Q3_ID Comment3 1 11 Row1 Comment1 12 Row1 Comment2 13 Row1 Comment3 2 21 Row2 Comment1 22 Row2 Comment2 23 Row2 Comment3 3 31 Row3 Comment1 32 Row3 Comment2 33 Row3 Comment3 4 41 Row4 Comment1 42 Row4 Comment2 43 Row4 Comment3

Then we can do some trickery with UNPIVOT

SELECT u2.ChkID, u2.Q_ID, u2.Comment FROM #TestingTable tt UNPIVOT ( AnswerID for Q_ID in (Q1_ID, Q2_ID, Q3_ID) ) u1 UNPIVOT ( Comment for Comment_ID in (Comment1, Comment2, Comment3) ) u2 WHERE SUBSTRING(u2.Q_ID,2,1) = RIGHT(Comment_ID,1)

Which gives us this result;

ChkID Q_ID Comment 1 Q1_ID Row1 Comment1 1 Q2_ID Row1 Comment2 1 Q3_ID Row1 Comment3 2 Q1_ID Row2 Comment1 2 Q2_ID Row2 Comment2 2 Q3_ID Row2 Comment3 3 Q1_ID Row3 Comment1 3 Q2_ID Row3 Comment2 3 Q3_ID Row3 Comment3 4 Q1_ID Row4 Comment1 4 Q2_ID Row4 Comment2 4 Q3_ID Row4 Comment3

Which is what I think you're after. You'll have to do a little work on that WHERE clause to fit the use case of 200+ columns (this only works for columns with a single digit length) and you'll probably want to put a clause there on the ChkID too. This should get you on your way if I've understood your question correctly.

If you're not sure how many columns you're going to have, then you'll want to do some dynamic querying to pass through your column list. Something like this;

Test Data (a temp table won't work this time because we're using sys.columns)

IF OBJECT_ID('TestingTable', 'U') IS NOT NULL DROP TABLE TestingTable; CREATE TABLE TestingTable ( ChkID int ,Q1_ID int ,Comment1 varchar(20) ,Q2_ID int ,Comment2 varchar(20) ,Q3_ID int ,Comment3 varchar(20) ,Q200_ID int ,Comment200 varchar(20) ) INSERT INTO TestingTable (ChkID, Q1_ID, Comment1, Q2_ID, Comment2, Q3_ID, Comment3, Q200_ID, Comment200) VALUES (1,11,'Row1 Comment1', 12, 'Row1 Comment2', 13, 'Row1 Comment3', 1200, 'Row1 Comment200') ,(2,21,'Row2 Comment1', 22, 'Row2 Comment2', 23, 'Row2 Comment3', 2200, 'Row1 Comment200') ,(3,31,'Row3 Comment1', 32, 'Row3 Comment2', 33, 'Row3 Comment3', 3200, 'Row1 Comment200') ,(4,41,'Row4 Comment1', 42, 'Row4 Comment2', 43, 'Row4 Comment3', 4200, 'Row1 Comment200')

Dynamic version of the query above

DECLARE @sql nvarchar(max) DECLARE @QuestionList nvarchar(max) DECLARE @CommentList nvarchar(max) SELECT @QuestionList = COALESCE(@QuestionList+',' , '') + c.name FROM sys.columns c WHERE c.object_id = OBJECT_ID('TestingTable') AND c.name LIKE 'Q%' AND c.name LIKE '%_ID' SELECT @CommentList = COALESCE(@CommentList+',' , '') + c.name FROM sys.columns c WHERE c.object_id = OBJECT_ID('TestingTable') AND c.name LIKE 'Comment%' SET @sql = N'SELECT u2.ChkID, u2.Q_ID, u2.Comment FROM TestingTable tt UNPIVOT ( AnswerID for Q_ID in ('+@QuestionList+') ) u1 UNPIVOT ( Comment for Comment_ID in ('+@CommentList+') ) u2 WHERE SUBSTRING(Q_ID,2,CHARINDEX(''_'',Q_ID)-2) = SUBSTRING(u2.Comment,CHARINDEX(''Comment'',u2.Comment)+7,3)' EXEC (@sql)

Output

ChkID Q_ID Comment 1 Q1_ID Row1 Comment1 1 Q2_ID Row1 Comment2 1 Q200_ID Row1 Comment200 1 Q3_ID Row1 Comment3 2 Q1_ID Row2 Comment1 2 Q2_ID Row2 Comment2 2 Q200_ID Row1 Comment200 2 Q3_ID Row2 Comment3 3 Q1_ID Row3 Comment1 3 Q2_ID Row3 Comment2 3 Q200_ID Row1 Comment200 3 Q3_ID Row3 Comment3 4 Q1_ID Row4 Comment1 4 Q2_ID Row4 Comment2 4 Q200_ID Row1 Comment200 4 Q3_ID Row4 Comment3

I've updated that last query to allow for Q_ID to go up into the hundreds, you may need to alter this depending upon your exact data structure.

Improve this answer