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.

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

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
* CodingMicrosoft SQL Server

Avatar of undefined
Last Comment
Chuck Lowe

8/22/2022 - Mon
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
    @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)') 

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
                for question_tx in (' + @cols + ')
            ) p '


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

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?
Shalu M

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chuck Lowe

Works Great. Thanks so much!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.