Dynamic SELECT and place result in variable columns
Adding an example to Michael's answer.  The problem with the PIVOT is two fold. 1st, it wants to aggregate.  You can get around this by defining your dataset to be distinct and using MAX or MIN functions.  But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question.  You would end up with only one row with the two datasets aggregated for the dupe user.  To get around this I added a "question_set" field, adding a date (distinct enough for this example) to each question/answer set.

2nd, you still have to define the pivoted fields.  If you have 50 questions, that's 50 definitions.  And if you add a question, you will have to add the definition to the query.  Using a loop, I created a question list dynamically and inserted it into the pivot query.  Hope this helps.

--&gt; Create the test data
if OBJECT_ID('QuestionAnswers','u') is not null
    drop table QuestionAnswers

create Table  QuestionAnswers
(
id int identity,
question_set date, --&gt; need something to delineate repeats
user_id varchar(255),
question_id int,
answer varchar(255)
)

if OBJECT_ID('Questions','u') is not null
    drop table Questions

create Table  Questions
(
id int identity,
question_id int,
question_name varchar(255)
)
go

with CTEquestion
as
(
    select 1 QID
    union all
    Select QID+1
    from CTEquestion
    where QID &lt; 11
)
insert questions
select QID, 'Question'+cast(QID as varchar(50))
from CTEquestion


insert QuestionAnswers
values ('2015-04-23', 'a1', 1, 'Canswer1')
    ,  ('2015-04-23', 'a1', 2, 'Ianswer2')
    ,  ('2015-04-23', 'a1', 3, 'Canswer3')
    ,  ('2015-04-23', 'a1', 4, 'Canswer4')
    ,  ('2015-04-23', 'a1', 5, 'Ianswer5')
    ,  ('2015-04-23', 'a1', 6, 'Ianswer6')
    ,  ('2015-04-23', 'a1', 7, 'Canswer7')
    ,  ('2015-04-23', 'a1', 8, 'Canswer8')
    ,  ('2015-04-23', 'a1', 9, 'Canswer9')
    ,  ('2015-04-23', 'a1', 10,'Canswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'b2', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 2, 'Canswer2')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'c3', 1, 'Ianswer1')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 4, 'Ianswer4')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'a1', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 6, 'Canswer6')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 10, 'Ianswer10')
--&gt;End test data creation

--straight join
select qa.user_id, qa.question_set, q.question_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id
order by qa.user_id


--dynamic pivot 
DECLARE 
    @questionList varchar(max)
, @maxQID int
, @qid int

select @questionList='',@maxQID = MAX(question_id), @qid= MIN(question_id)
FROM Questions

while @qid &lt;= @maxQID
begin
    set @questionList=@questionList+'['+cast(@qid as varchar(10))+']'

    select @qid=min(question_id)
    from Questions
    where question_id &gt; @qid

    if @qid&lt;=@maxQID
        set @questionList=@questionList+', '
end

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
select user_id, '+@questionList+'
from
(select q.question_id, qa.question_set, qa.user_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id) x
PIVOT
(
max(answer)
FOR question_id in ('+@questionList+')
) pvt
order by user_id'

exec sp_executesql @SQL