Simple SQLServer PIVOT/Transposed query, how to wr

问题:

I have a SELECT which is returning me data in the following form...

ID             Question             Answer
1              Any Good?            Yes
1              Happy?               No
1              Good Staff?          Yes
1              Return?              N/A
2              Any Good?            No
2              Happy?               No
2              Good Staff?          Yes
2              Return               N/A
...

I need this in the following form...

ID       Any Good?   Happy?   Good Staff?   Return?
1        Yes         No       Yes           N/A
2        No          No       Yes           N/A
...

The 'Answer' column in my top query is computed using a CASE .. WHEN. I was thinking maybe the PIVOT clause could help me. This seems to require aggregation though. I don't need to aggregate, just transpose.

I don't mind having to specify each Answer explicitly in the solution (I'm guessing I'll have to do that anyway).

Anyone know the best way of doing this?

回答1:

Have you tried something like

SELECT  *
FROM    (
            SELECT  ID,
                    Question,
                    Answer
            FROM    @Table1
        ) t
PIVOT (MAX(Answer) FOR Question IN ([Any Good?],[Happy?],[Good Staff?],[Return?])) p

回答2:

The pivot operation requires you to use some form of aggregate, however if you will only have one value Max() will grab the max (only) value for you

Something Like this should work

Select *
from Table
Pivot
(
    Max(answer)
    For Question In ([Any Good?],[Happy?],[Good Staff?],[Return?])
)
AS P