Select column values as column names

Question

I have the following table: tb_answers

The table structure is as follows

ID | QUESTIONID | SURVEYID | LEAGUEID | MEMBERID | ANSWER | DATEADDED | DIVISIONID

Sample data

302347 | 2336 | 172 | 146 | 68 | 467 Vallie Lane | 2012-05-21 16:36:22.433 | 0
302350 | 2340 | 172 | 146 | 68 | 591             | 2012-05-21 16:36:22.483 591

I need to present this data as follows.

DYNNAMIC LIST OF QUESTION#QUESTIONID# | MEMBERID | DIVISION
DYNNAMIC LIST OF VALUES OF QUESTION#QUESTIONID#|#MEMBERID#|DIVISION

So I can sort by the value of QUESTION#QUESTIONID#

This possible? I'm not good with pivot and union, so any help is appreciated.

It's not exactly clear what you want the result to be, please edit your original question and show the desired result using the sample data you provided. — Taryn, Mar 28 '13 at 00:33

Peter Wone · Answer 1 · 2013-03-28T00:58:38.537

This is the official MS example:

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

That's a pivot of the data from this:

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

which produces this

DaysToManufacture          AverageCost    
0                          5.0885    
1                          223.88    
2                          359.1082    
4                          949.4105

As you can see they've specified the pivoted columns explicitly - it's not data-driven. To ge things totally data driven you essentially have to do two passes, the first one generating the column names into a string template. The second pass executes that generated SQL.

DECLARE @dyncols AS VARCHAR(MAX) = (SELECT 
  STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(50), YOURPIVOTCOL)) 
  FROM YOURTABLE FOR XML PATH(''), TYPE).VALUE('.', 'VARCHAR(MAX)'),1,1,''))

Select column values as column names

1 Answers1