简单的 SQLServer PIVOT/Transposed 查询，怎么写? [英] Simple SQLServer PIVOT/Transposed query, how to write?

问题描述

我有一个 SELECT，它以以下形式向我返回数据...

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
...

我的顶部查询中的答案"列是使用 CASE .. WHEN 计算的.我在想也许 PIVOT 子句可以帮助我.不过，这似乎需要聚合.我不需要聚合，只需转置.

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.

我不介意必须在解决方案中明确指定每个 Answer(我猜我还是必须这样做).

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?

推荐答案

你有没有尝试过类似的方法

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

这篇关于简单的 SQLServer PIVOT/Transposed 查询，怎么写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！