1

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

  • 1
    Join your two tables. then use PIVOT to convert the rows into columns, giving the format you desire. The blanks will come out as NULL. – Michael Green Apr 24 '15 at 11:35
1

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.

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

create Table  QuestionAnswers
(
id int identity,
question_set date, --> 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 < 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')
-->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 <= @maxQID
begin
    set @questionList=@questionList+'['+cast(@qid as varchar(10))+']'

    select @qid=min(question_id)
    from Questions
    where question_id > @qid

    if @qid<=@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
1

I use something like this to create dynamic pivots with a dynamic number of columns. Perhaps it will help you with your solution. It's a single statement to avoid looping or cursors.

declare @strSQL as varchar(max)
declare @columns as varchar(max)
declare @columns1 as varchar(max)

set @columns = (select stuff ((select ',cast(' + quotename(columnname) + ' as ' + fieldDataType + ') as ' + quotename(fieldname) as [text()] from MyTable for xml path ('') ),1,1,''))
set @columns1 = (select stuff ((select ',' + quotename(columnnumber) as [text()] from MyTable for xml path ('') ),1,1,''))


set @strSQL = 'select rownumber, ' + @columns + ' from 
    (select rownumber, columnnumber, fieldData from MyTable  
    pivot
    ( max(fieldData) for columnNumber in (' + @columns1 + ')) p'


exec (@strSQL)
  • Thanks Phil, new to the site and not sure about how to correctly format the answers yet. – Erin Ramsay Apr 24 '15 at 17:17
  • Welcome. :) And good on you for having a go :) – Sir Swears-a-lot Apr 30 '15 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.