asked on

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

Shalu M

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

Chuck Lowe

ASKER

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?

ASKER CERTIFIED SOLUTION

Shalu M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chuck Lowe

ASKER

Works Great. Thanks so much!