How to Transpose a resultset from SQL

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   开发者_StackOverflow中文版  |     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.

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.

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.

PIVOT is a start, but the thing with sql queries is that you really need to know what columns to expect in the result set before writing the query. If you don't know this, the last time I checked you have to either resort to dynamic sql or allow the client app that retrieves the data to do the pivot instead.