select id with text from table into view

Question

I have a view taken column from table question and table answer. table question have id and question text while table answer have id, question_id(fk from table question) and answer text.

SELECT     
    QuestionID, QuestionTxt, [1], [2], [3], [4]
FROM         
    (SELECT     
         ROW_NUMBER() OVER (PARTITION BY QuestionID
                            ORDER BY newid()) AnswerInQuestionID, 
         a.AnswerTxt, q.QuestionTxt, q.QuestionId
     FROM         
         dbo.TblQuestion q 
     JOIN
         dbo.TblAnswer a ON q.QuestionId = a.answer_question_id) A 
PIVOT (MAX(a.AnswerTxt) FOR AnswerInQuestionID IN ([1], [2], [3], [4])) AS piv

From this SQL, I am able to randomize the answer but it only shows the text. I want the the text answer together with its id

possible duplicate of [Select n random rows from SQL Server table](http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) — vittore, May 23 '14 at 15:07
nope. this sql is entirely have no problem. i just want to add the answer id into the view — Jimmy, May 23 '14 at 15:17

AHiggins · Accepted Answer · 2014-05-23T17:43:45.143

1

It's hard to tell from your question where you are hoping to get the AnswerID. If you want it in a single column, you will need to use @StayPuft's answer. If you want it as part of the text column, you can use the SQL below:

SELECT questionText, [1], [2], [3]
FROM
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY newid()) AnswerInQuestionID,
        CAST(AnswerID AS VARCHAR(20)) + ' - ' + answerTxt AS answerTxt, 
        QuestionText
    FROM questions q
        JOIN answers a
            ON q.QuestionID=a.answer_question_id
) A
PIVOT
(
    MAX(answerTxt)
    FOR AnswerInQuestionID IN ([1], [2], [3] )
) as piv

If you want them as three separate columns, you can use the SQL below:

SELECT 
  questionText, 
  LEFT([1], 1) AS AnswerID1,
  RIGHT([1], LEN([1]) - 1) AS AnswerText1,
  LEFT([2], 1) AS AnswerID2,
  RIGHT([1], LEN([1]) - 1) AS AnswerText1,
  LEFT([3], 1) AS AnswerID3,
  RIGHT([2], LEN([3]) - 1) AS AnswerText3
FROM
(
  SELECT 
  ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY newid()) AnswerInQuestionID,
  CAST(AnswerID AS VARCHAR(20)) + answerTxt AS answerTxt, 
  QuestionText
  FROM questions q
  JOIN answers a
  ON q.QuestionID=a.answer_question_id
) A
PIVOT
(
  MAX(answerTxt)
  FOR AnswerInQuestionID IN ([1], [2], [3] )
) as piv

EDIT: Added CHARINDEX() function and pipes ('|') to allow for varying number length. There are probably cleaner ways of doing this, but this worked for me:

SELECT 
  questionText, 
  LEFT([1], CHARINDEX('|', [1]) -1) AS AnswerID1,
  RIGHT([1], LEN([1]) - CHARINDEX('|', [1])) AS AnswerText1,
  LEFT([2], CHARINDEX('|', [2]) -1) AS AnswerID2,
  RIGHT([2], LEN([2]) -  CHARINDEX('|', [2])) AS AnswerText2,
  LEFT([3], CHARINDEX('|', [3]) -1) AS AnswerID3,
  RIGHT([3], LEN([3]) -  CHARINDEX('|', [3])) AS AnswerText3
FROM
(
  SELECT 
  ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY newid()) AnswerInQuestionID,
  CAST(AnswerID AS VARCHAR(20)) +'|'+ answerTxt AS answerTxt, 
  QuestionText
  FROM questions q
  JOIN answers a
  ON q.QuestionID=a.answer_question_id
) A
PIVOT
(
  MAX(answerTxt)
  FOR AnswerInQuestionID IN ([1], [2], [3] )
) as piv

edited May 23 '14 at 17:43

answered May 23 '14 at 16:36

AHiggins

7,029
6
36
54

Out of curiosity, which version was the one that you were looking for? It would be great if you could update your original post with that detail, so that future searchers can find the answer to similar problems. – AHiggins May 23 '14 at 17:36
i did some [modification](http://sqlfiddle.com/#!3/a1739/54) according to your sql but it have problem if the id have more than one numeric. see [here](http://sqlfiddle.com/#!3/9c90c0/1) – Jimmy May 23 '14 at 17:36
Good point - I think you should be able to use some kind of substring function to strip out the difference, I'll update accordingly. – AHiggins May 23 '14 at 17:38
Added CHARINDEX() and pipes to the query. – AHiggins May 23 '14 at 17:44
i got error invalid length parameter passed to the LEFT or substring function – Jimmy May 23 '14 at 18:02
Are there NULL or zero-length values in your data? – AHiggins May 23 '14 at 18:04
The error is probably happening because the CHARINDEX() -1 step is returning a negative value from the query. That shouldn't happen in this because every instance of `CAST(AnswerID AS VARCHAR(20)) +'|'+ answerTxt` should contain the pipe. Are you doing a LEFT OUTER JOIN? Can you run a `SELECT CAST(AnswerID AS VARCHAR(20)) +'|'+ answerTxt` on your two tables and confirm that every single value returned contains the pipe? – AHiggins May 23 '14 at 18:12
it is confirm that every single value return the pipe – Jimmy May 23 '14 at 18:27
The only way I could recreate the problem was by looking at the wrong field with the LEN() function (i.e., `RIGHT([3], LEN([1]) - CHARINDEX('|', [3]))`). Did you add any more columns to the query before testing it, or change any column names? If you made changes, can you post the new SQL? – AHiggins May 23 '14 at 18:29
Change your SELECT statement to `SELECT *` and add a WHERE clause at the very end (after "as piv"): `WHERE CHARINDEX('|', [1]) = 0 OR CHARINDEX('|', [2]) = 0 OR CHARINDEX('|', [3]) = 0`. Any results? – AHiggins May 23 '14 at 18:39
the answer text is append after the id – Jimmy May 23 '14 at 18:45
But you got an output? That query was not intended as a solution, just an attempt to debug - if you returned any values, those are the rows that are causing an error by returning a 0 to `CHARINDEX()`. Can you post an example of a row that is returned by that? I'd like to move this to a chat session, but you don't have the rep on SO to do one. – AHiggins May 23 '14 at 18:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54302/discussion-between-sheldon-and-ahiggins). – Jimmy May 23 '14 at 18:57

score 0 · Answer 2 · answered May 23 '14 at 15:07

0

It looks like a.AnswerTxt is not in your main select, just QuestionID and QuestionTxt. Add a.AnswerTxt to the first line.

answered May 23 '14 at 15:07

RyanB

757
4
11

this sql is actually have no error. i only want to add the answer id into the view. refer [sql fiddle](http://sqlfiddle.com/#!3/a1739/1) – Jimmy May 23 '14 at 15:19

score 0 · Answer 3 · answered May 23 '14 at 15:44

0

First let me say that SQL Fiddle is awesome!

And if you wanted to display the answer ID - this is what I did:

SELECT questionText, [1], [2], [3], answerID
FROM
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY newid()) AnswerInQuestionID,
    answerTxt,
    QuestionText,
    answerID
FROM questions q
    JOIN answers a
        ON q.QuestionID=a.answer_question_id
   ) A
    PIVOT
    (
        MAX(answerTxt)
        FOR AnswerInQuestionID IN ([1], [2], [3] )
    ) as piv

answered May 23 '14 at 15:44

StayPuft

125
1
1
13

this is totally wrong. i've tried your sql into sql fiddle [this](http://sqlfiddle.com/#!3/a1739/19) is the result – Jimmy May 23 '14 at 15:54
Its actually shows all of the answerids in the table answers - it just kind of rips the table apart to appropriately display the answer to the correct id :P Answer1 - ID 1 or 4 etc. Exactly ow do you even want to display the IDs? – StayPuft May 23 '14 at 16:13
You say it's wrong, but you haven't provided a sample of what you want your output to be. This solution does what you asked, which is adding an AnswerID column to your view. Are you trying to add a single column to the view, or multiple columns? If you want a single column, it's going to have to maintain the original number of Answer rows. – AHiggins May 23 '14 at 16:38

select id with text from table into view

3 Answers3