Link to home
Create AccountLog in
Avatar of Chuck Lowe
Chuck Lowe

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
Avatar of Shalu M
Shalu M
Flag of United States of America image

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

Avatar of Chuck Lowe
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
Avatar of Shalu M
Shalu M
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Works Great. Thanks so much!