I need to Convert multiple rows to columns with specified heading in SQLServer

Chuck Lowe
Chuck Lowe used Ask the Experts™
on
I need to take a table that contains x number of rows so hard coding is not a solution. I need to put the
data from the column as a heading onto an excel report. Then I need to get data from another table and populate that
as the row answers.

tbl_Survey_Questions
Survey_id      int (key)
Question_id      int (key)
Question_TX      varchar(100)

tbl_Survey_Answers
Survey_id      int (key)
Question_id      int (key)
Answer_id      int
Answer_tx      varchar(50)

The heading would be Question_TX (as stated there could be any number of rows for each Survey).
The answer under the heading is Answer_TX, which could be any number of rows. I know how to code this but not sure
if it changes based on how I would get the heading. Survey_ID and Question_ID is the keys to link up.
This would go on an excel report I'm coding in T-SQL and actually will be placed in the middle of the report with other data.
I'm on SQLServer 2012
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Here's one way to accomplish this -

create table dbo.tbl_Survey_Questions(
Survey_id      int,
Question_id      int, 
Question_TX      varchar(1000)
);

create table dbo.tbl_Survey_Answers(
Survey_id      int ,
Question_id    int, 
Answer_id      int,
Answer_tx      varchar(50)
);

insert into dbo.tbl_Survey_Questions values (1, 1, 'Question 1');
insert into dbo.tbl_Survey_Questions values (1, 2, 'Question 2');
insert into dbo.tbl_Survey_Questions values (1, 3, 'Question 3');
insert into dbo.tbl_Survey_Questions values (1, 4, 'Question 4');

create table dbo.tbl_Survey_Answers(
Survey_id      int ,
Question_id    int, 
Answer_id      int,
Answer_tx      varchar(50)
);


insert into dbo.tbl_Survey_Answers values (1, 1, 1, 'Question 1 Answer 1');
insert into dbo.tbl_Survey_Answers values (1, 1, 2, 'Question 1 Answer 2');
insert into dbo.tbl_Survey_Answers values (1, 2, 3, 'Question 2 Answer 1');
insert into dbo.tbl_Survey_Answers values (1, 4, 4, 'Question 4 Answer 1');

Open in new window


The first part of the below query gets all the questions from the questions table and then uses pivot to get the results
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(q.question_tx) 
            FROM tbl_Survey_Questions q
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
            (
                select question_tx
                    , answer_tx
                from dbo.tbl_Survey_Questions q
left join dbo.tbl_Survey_Answers a on a.Survey_id = q.survey_id and a.question_id = q.question_id

           ) x
            pivot 
            (
                 max(answer_tx)
                for question_tx in (' + @cols + ')
            ) p '


execute(@query)

Open in new window


Results -

Question 1                                         Question 2                                         Question 3                                         Question 4
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Question 1 Answer 2                                Question 2 Answer 1                                NULL                                               Question 4 Answer 1

Open in new window

Author

Commented:
Thanks. This works except I need it to sort by Survey_id ,Question_id  ,Answer_id  and I get and error that I can't use an Order by
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta"
Where should it go?
Commented:
This should do it -
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(q.question_tx) 
            FROM tbl_Survey_Questions q
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ' WITH DATA AS (
					select TOP (100) PERCENT q.question_tx, a.answer_tx
					from dbo.tbl_Survey_Questions q
					left join dbo.tbl_Survey_Answers a on a.Survey_id = q.survey_id 
					and a.question_id = q.question_id
					order by q.Survey_id , a.Question_id  ,a.Answer_id
				) 
				SELECT distinct ' + @cols + ' from DATA x
				pivot 
				(
					 max(x.answer_tx)
					for x.question_tx in (' + @cols + ')
				) p'
				


execute(@query)

Open in new window

Author

Commented:
Works Great. Thanks so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial