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

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
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shalu MCommented:
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

0
Chuck LoweAuthor 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?
0
Shalu MCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
Works Great. Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Coding

From novice to tech pro — start learning today.