Dynamic SELECT and place result in variable columns Edit: I use Microsoft SQL Server Management Studio, I believe it's SQL 2008R2. Sorry, new to this.

I've got a complex question, at least, I think so since I can't find the answer after excessive googling.

Simplified situation:

Table | QuestionAnswers
id
user_id
question_id
answer

Table | Questions
id
question_id
question_name


Now I want to write a query that gives the following result:

user_id | question 1 | question 2 | question 3 | que...etc
1         Something
1                                   Something
2                      Something       
3         Something                
3                      Something


Or (but that shouldn't be too difficult if I have the above?):

user_id | question 1 | question 2 | question 3 | que...etc
1         Something                 Something
2                      Something
3         Something    Something


Where 'something' is the answer to the question.

Now I have a possible query:

SELECT [user_id]    
      ,[answer] as Question1
      ,''       as Question2
      ,etc
FROM [QuestionAnswers]    
WHERE [question_id] = 1

UNION ALL

SELECT [user_id]    
       ,''       as Question1
       ,[answer] as Question2
       ,etc
FROM [QuestionAnswers]     
WHERE [question_id] = 2

UNION ALL

SELECT etc...


Works like a charm. Downside is that this way, I have to make x SELECT statements with x rows, where x is the number of questions I have (50+).

How can this be done dynamically? Is that possible?

I hope I explained myself good enough...

Kind regards,
Tjab
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