SQL query to return a report from two tables

Question

I have two tables, Answers and Users , which look like this:

 Answers (AnswerID, QuestionNumber, Answer, UserID) Users (UserID, FirstName, LastName) 

Which SQL query can be run to return a report as follows:

Answer 1

You can use the PIVOT function to convert this data. It seems rather unusual that there is a Name in the headings, but if that is what you want, you can use the following:

 select * from ( select a.questionnumber, u.firstname +' '+ u.lastname as Name, a.answer from answers a left join users u on a.userid = u.userid ) src pivot ( max(answer) for name in ([John Smith], [Bob Jones]) ) piv; 

See SQL Fiddle with Demo

Result:

 | QUESTIONNUMBER | JOHN SMITH | BOB JONES | ------------------------------------------- | 1 | blah | test | | 2 | hsdfk | (null) | 

I would think that you want Question Numbers be headers with answers below them:

 select * from ( select 'QuestionNumber'+cast(a.questionnumber as varchar(10)) questionnumber, u.firstname +' '+ u.lastname as Name, a.answer from answers a left join users u on a.userid = u.userid ) src pivot ( max(answer) for questionnumber in ([QuestionNumber1], [QuestionNumber2]) ) piv; 

See SQL Fiddle with Demo

Result:

 | NAME | QUESTIONNUMBER1 | QUESTIONNUMBER2 | -------------------------------------------------- | Bob Jones | test | (null) | | John Smith | blah | hsdfk | 

The above answers will work just fine if you have a known number of elements to convert. But if you have an unknown number, you can use dynamic SQL for PIVOT data:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME('QuestionNumber'+cast(questionnumber as varchar(10))) from answers FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT Name, ' + @cols + ' from ( select ''QuestionNumber''+cast(a.questionnumber as varchar(10)) questionnumber, u.firstname +'' ''+ u.lastname as Name, a.answer from answers a left join users u on a.userid = u.userid ) x pivot ( max(answer) for questionnumber in (' + @cols + ') ) p ' execute(@query) 

See SQL Fiddle with Demo

Result:

 | NAME | QUESTIONNUMBER1 | QUESTIONNUMBER2 | -------------------------------------------------- | Bob Jones | test | (null) | | John Smith | blah | hsdfk | 

Answer 2

This is called a cross-tab request — there are many examples of how to create them; eg:

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

EDIT

@Bluefleet using pivot point is probably the best answer; using a simple cross tab may require you to know some aspects of the data form in advance, so the columns are essentially hardcoded - if the columns are stable, then the following should work:

 declare @Answers table (AnswerID int, QuestionNumber int , Answer int , UserID int) declare @Users table (UserID int , FirstName varchar(10), LastName varchar(10)) declare @Questions table (QuestionNumber int, QuestionName varchar(10)) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (1, 1, 10, 1) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (2, 2, 20, 1) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (3, 3, 30, 2) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (1, 1, 40, 2) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (2, 2, 50, 2) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (3, 3, 60, 3) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (1, 1, 70, 3) insert into @Answers (AnswerID, QuestionNumber, Answer, UserID) values (2, 2, 80, 3) insert into @Users (UserID, FirstName, LastName) values (1, 'Tom', 'Smith') insert into @Users (UserID, FirstName, LastName) values (2, 'Dick', 'Brown') insert into @Users (UserID, FirstName, LastName) values (3, 'Harry', 'Robinson') insert into @Questions (QuestionNumber, QuestionName) values (1, 'Question 1') insert into @Questions (QuestionNumber, QuestionName) values (2, 'Question 2') insert into @Questions (QuestionNumber, QuestionName) values (3, 'Question 3') select QuestionName, MAX(User1) as 'Tom Smith', MAX(User2) as 'Dick Brown', MAX(User3) as 'Harry Robinson' from ( select q.QuestionName, (case when a.UserID=1 then Answer else null end) as 'User1', (case when a.UserID=2 then Answer else null end) as 'User2', (case when a.UserID=3 then Answer else null end) as 'User3' from @Answers a join @Users u on u.UserID = a.UserID join @Questions q on q.QuestionNumber = a.QuestionNumber ) as combined group by QuestionName QuestionName Tom Smith Dick Brown Harry Robinson ------------ ----------- ----------- -------------- Question 1 10 40 70 Question 2 20 50 80 Question 3 NULL 30 60