将数据转换为存储桶 [英] Transpose Data into Buckets
问题描述
我在两列表中有用户数据，它将用户在企业数据库中的角色联系起来。他们可以从150个左右的角色中选择最多20个角色。该表在概念上看起来如下（我为格式道歉）：
用户.....角色....
John ... ..角色1
约翰.....角色45
罗恩......角色1
罗恩.... ..角色25
罗恩......角色30
肖恩......角色41
肖恩......角色42
肖恩......角色43
肖恩......角色44
肖恩......角色45
................
（Shawn继续担任角色60）
...... ..........
Shawn ....角色60
我想创建一个创建20个桶的表来填充这个人拥有的每个角色。
用户..... Bucket 1..Bucket 2..Bucket 3 ....... Bucket 20
John .....角色1 ....角色45
Ron ......角色1 ....角色25 ...角色30
Shawn ....角色41 ......角色42 ......角色43 ........角色60
我知道关系智能数据库不是'不喜欢这样，但我有充分的理由知道为什么我需要这样做（至少是啊我这样做）。任何帮助将不胜感激。一个简单的交叉表不会因为150个左右的标题和所有空白字段而做。我需要bucketize它像肯德基...
谢谢，
Omar
Hi,
I have user data in a two column table that relates roles that users have in a enterprise database. They can choose up to 20 roles out of 150 or so roles. The table conceptually looks like the following (i apologize for the format):
User.....Role....
John.....Role 1
John.....Role 45
Ron......Role 1
Ron......Role 25
Ron......Role 30
Shawn....Role 41
Shawn....Role 42
Shawn....Role 43
Shawn....Role 44
Shawn....Role 45
................
(Shawn continues up to Role 60)
................
Shawn....Role 60
I would like to create a table that creates 20 buckets that fill with each role that the person has.
User.....Bucket 1..Bucket 2..Bucket 3.......Bucket 20
John.....Role 1....Role 45
Ron......Role 1....Role 25...Role 30
Shawn....Role 41...Role 42...Role 43........Role 60
I know relationship-wise databases don''t like this, but i have a good reason as to why i need to do this (at least i hope i do). any help would be greatly appreciated. A simple crosstab won''t do because of the 150 or so headers and all the blank fields. i need to "bucketize" it like KFC...
Thanks,
Omar
推荐答案
如果你加入了表格本身，你可以设置一个peudo级别，然后你可以使用它来转动。
假设用户和角色的组合是唯一的，你可以做这样的事情。
If you join the table to itself, you can set up a peudo-rank that you can then use to pivot.
Assuming that the combination of User and Role is unique, you could do something like this.
我创建了以下SQL语句：
SELECT [CC Security] .User，Max（[CC安全]！[CC安全规则]）AS RuleMod，Count（*）AS PseudoRank
FRO M [CC安全] AS x，[CC安全] AS y
WHERE（x.User = y.User）AND（x。[CC安全规则]< = y。[CC安全规则]）;
我得到两个对话框，要求输入参数值，一个用于CC Security.User，另一个用于CC Security！CC安全规则
离开空白并单击确定后，我得到结果：
列：User，RuleMod，PseudoRank，唯一的值是PseudoRank中的数字（824）。
我想我明白你要去哪里，但我不知道如何使查询工作。
我会在此期间继续努力。
非常感谢您的帮助。
I''ve created the following SQL statement:
SELECT [CC Security].User, Max([CC Security]![CC Security Rule]) AS RuleMod, Count(*) AS PseudoRank
FROM [CC Security] AS x, [CC Security] AS y
WHERE (x.User = y.User) AND (x.[CC Security Rule]<= y.[CC Security Rule]);
I get two dialogue boxes asking to Enter Parameter Values, one for CC Security.User, and another for CC Security!CC Security Rule
After leaving the blank and clicking ok, i get the result:
Columns: User, RuleMod, PseudoRank and the only value is a number in PseudoRank (824).
I think I understand where you''re going with this, but i can''t figure out how to make the query work.
I''ll keep trying in the meantime.
Thanks so much for your help.
您使用的是SQL Server还是MS Access？
~~ CK
Are you using SQL Server or MS Access?
~~ CK
这篇关于将数据转换为存储桶的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
