How to Transpose a resultset from SQL

Question

I am using Microsoft SQL Server 2008.
I have a table that looks something like this:

|======================================================|
| RespondentId | QuestionId | AnswerValue | ColumnName | 
|======================================================|
|     P123     |      1     |     Y       | CanBathe   |
|------------------------------------------------------|
|     P123     |      2     |     3       | TimesADay  |
|------------------------------------------------------|
|     P123     |      3     |     1.00    | SoapPrice  |
|------------------------------------------------------|
|     P465     |      1     |     Y       | CanBathe   |
|------------------------------------------------------|
|     P465     |      2     |     1       | TimesADay  |
|------------------------------------------------------|
|     P465     |      3     |     0.99    | SoapPrice  |
|------------------------------------------------------|
|     P901     |      1     |     N       | CanBathe   |
|------------------------------------------------------|
|     P901     |      2     |     0       | TimesADay  |
|------------------------------------------------------|
|     P901     |      3     |     0.00    | SoapPrice  |
|------------------------------------------------------|

I would like to flip the rows to be columns so that this table looks like this:

|=================================================|
| RespondentId | CanBathe | TimesADay | SoapPrice | 
|=================================================|
|     P123     |     Y    |      3    |   1.00    | 
|-------------------------------------------------|
|     P465     |     Y    |      1    |   0.99    | 
|-------------------------------------------------|
|     P901     |     N    |      0    |   0.00    | 
|-------------------------------------------------|

(the example data here is arbitrarily made up, so its silly)

The source table is a temp table with approximately 70,000 rows.
What SQL would I need to write to do this?

Update

• I don't even know if PIVOT is the right way to go.
• I don't know what column to PIVOT on.
• The documentation mentions <aggregation function> and <column being aggregated> and I don't want to aggregate anything.

Thanks in advance.

Answer 1

It, is required to use an aggregate function if you use PIVOT. However, since your (RespondentId, QuestionId) combination is unique, your "groups" will have only one row, so you can use MIN() as an aggregate function:

SELECT RespondentId, CanBathe, TimesADay, SoapPrice
FROM (SELECT RespondentId, ColumnName, AnswerValue FROM MyTable) AS src
PIVOT (MIN(AnswerValue) FOR ColumnName IN(CanBathe, TimesADay, SoapPrice)) AS pvt

If a group only contain one row, then MIN(value) = value, or in other words: the aggregate function becomes the identity function.

Answer 2

See if this gets you started. Used to have to use CASE statements to make that happen but it looks like some inkling of PIVOT is in SQL Server now.