select sql table rows as columns for survey application

Question

I am developing a survey application, a very simple one that has two tables.

table_survey_answers

+------------+------------+----------------+
| customerid | questionID |     answer     |
+------------+------------+----------------+
|          1 |        100 | Good           |
|          1 |        101 | Acceptable     |
|          1 |        102 | Excellent      |
|          2 |        100 | Not acceptable |
|          2 |        101 | Acceptable     |
|          2 |        102 | Good           |
+------------+------------+----------------+

table_questions

+------------+-----------------------------------+
| QuestionID |             Question              |
+------------+-----------------------------------+
|        100 | Kindly rate our customer service? |
|        101 | How fast is our product delivery? |
|        102 | Quality of the Product A?         |
+------------+-----------------------------------+

Now I want display survey result as follow in asp.net gridview.

+------------+-----------------------------------+-----------------------------------+---------------------------+
| CustomerID | Kindly rate our customer service? | How fast is our product delivery? | Quality of the Product A? |
+------------+-----------------------------------+-----------------------------------+---------------------------+
|          1 | Good                              | Acceptable                        | Excellent                 |
|          2 | Not Acceptable                    | acceptable                        | Good                      |
+------------+-----------------------------------+-----------------------------------+---------------------------+

I already created tables to get survey responses. Only thing I want export the result in gridview as explained above format.

You should look up PIVOT, you even tagged it. A quick search on this site will reveal hundreds of questions on this exact topic with dozens of responses each. — Sean Lange, Dec 15 '14 at 16:25
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — crthompson, Dec 15 '14 at 16:27
Sean Lange.. I have no idea.. How to convert questions value into column header.. — mbdAli, Dec 15 '14 at 16:27

Pரதீப் · Accepted Answer · 2014-12-15T16:36:14.947

0

Use Pivot which will transpose your rows to columns

SELECT *
FROM   (SELECT customerid,
               answer,
               Question
        FROM   table_questions a
               JOIN table_survey_answers b
                 ON a.QuestionID = b.questionID) a
       PIVOT (Max(answer)
             FOR Question IN([Kindly rate our customer service?],
                             [How fast is our product delivery?],
                             [Quality of the Product A?])) piv

SQL FIDDLE DEMO

edited Dec 15 '14 at 16:36

answered Dec 15 '14 at 16:30

Pரதீப்

91,748
19
131
172

The problem with that approach is that the ASP grid may not like some of the values returned as it will be using Data values as headers. (e.g. may have to url encode data first). You will still have problems if any row values begin with a number etc. – keitn Dec 15 '14 at 16:33
@keitn looks like this is what OP's expected output!! – Pரதீப் Dec 15 '14 at 16:37

select sql table rows as columns for survey application

1 Answers1