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

问题描述

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

  ID问题答案
 1任何好的？是
 1快乐？否
 1好工作人员？是
 1返回？ N / A 
 2任何好？否
 2快乐？否
 2好工作人员？是
 2返回N / A 
 ... 
  

以下表单...

  ID任何好的？快乐？好员工？返回？ 
 1是否是N / A 
 2否否是N / A 
 ... 
  

$ b b

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

我不介意在解决方案中明确指定每个回答（我猜我会

解决方案

/ div>

您尝试过

  SELECT * 
 FROM（
 SELECT ID， 
问题，
答案
 FROM @ Table1 
）t 
 PIVOT（MAX（Answer）FOR Question IN（[Any Good？]，[Happy？]，好员工？]，[Return？]））p 
  

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?

解决方案

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 /转置查询，怎么写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！