在数据透视查询的构造中需要帮助. [英] Need help in Pivot Query construction.

问题描述

在以下情况下进行PIVOT查询时，我需要一些帮助:
我的数据如下所示:

Hi ,

I need some help in making a PIVOT query for the following situation:
My data looks like this:

    ID   Participant  Representative  Manager
1   100  Orange       Mango           Banana
2   200  Mango        Banana          Orange
3   300  Null         Null            Mango
4   400  Banana       Null            Null
5   500  Orange       Banana          Null

我想通过以下方式:

I want it the following way:

    ID   Participant-Orange Participant-Mango Participant-Banana....
1   100   1                 
2   200                          1                                 
3   300                    
4   400                                             1             
5   500   1             

等等，代表和经理的组合及其下面的数量分别用于Orange，Banana，Mango ..

尝试使用数据透视查询进行操作，但这只能帮助我以这种方式创建标头.所有计数都显示为null，这不是我想要的.
有人可以通过查询帮助我实现此数据集.

[edit]从主题[/edit]中删除了紧急！"

[edit]已添加代码块，忽略HTML ..."选项已禁用，主题长度增加到30个以上字符-OriginalGriff [/edit]

and so on for the combinations of Representatives and Manager and their count below it for Orange,Banana,Mango..

Tried doin it using Pivot Query but that can only help me create headers this way..the count for all shows null which is not what i want.
Can someone please help me with the query to achieve this dataset.

[edit] removed ''Urgent!'' from subject [/edit]

[edit]Code block added, "Ignore HTML..." option disabled, subject length increased to 30+ characters- OriginalGriff[/edit]

推荐答案

如果您的表格名称为test，而您的主键字段(唯一值)为keyid，然后使用

If your table name is test and your primary key filed (unique values) is keyid then use

SELECT * FROM Test
 PIVOT (COUNT(keyid) FOR participant  IN ([Orrange],[Mango],[Banana])) p

在您的情况下，第一列似乎是唯一的.用它来计数.

in you case the first column seems unique. Use that to count.

这篇关于在数据透视查询的构造中需要帮助.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！