编写查询以更改数据的呈现方式 [英] Writing Query to change the way that the data is presented
问题描述
嗨
我们正在尝试创建一个动态电子表格，它将从CRM / SQL中提取信息。
我们在创建SQL查询方面的期限非常有限，我们非常感谢您的帮助。我们在创建初始查询方面有一些帮助，如下所示：
SELECT equipment.name，
equipment.new_modelno，
equipment.new_serialno，
equipment.new_type，
equipment.new_make，
equipment.new_mast，
equipment .new_capacity，
activitypartycustomer.partyidname，
serviceappointment.scheduledstart，
serviceappointment.scheduledend
来自FilteredEquipment AS设备
INNER JOIN FilteredActivityParty AS活动派对
ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer
ON serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participatio ntypemask = 11
WHERE（serviceappointment.statecode = 0）
ORDER BY equipment.name
此查询完全给了我们我们想要的信息但是我们注意到已经安排多次的资源在第二行的报告上出现两次。
我们希望资源一次列出，并且额外的服务活动详细信息显示在报告的其他列中。
For例如，我们将拥有设备名称，设备型号，设备品牌，设备序列号，服务活动1的客户名称，服务活动1的日期，服务活动的客户名称2以及服务活动的日期2.所有这些字段都会出现因为列和完整的细节不会在第二行重复。
例如
设备名称设备SerialNo客户开始日期结束日期
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS
EquipName EquipSerialNo Cust开始日期结束日期Cust开始日期结束日期
E15,3232，ABC Shoes，5-01-2007,30-06-2007， ABC Shoes，1-08-2007,30-04-2007
这可能实现吗？我们可以用某种方式编辑查询给我们
所需的信息吗？
真的很感激任何帮助。 。
非常感谢
Mark
Hi
我们正在尝试创建一个动态电子表格，它将从CRM / SQL中提取信息。
我们在创建SQL查询方面的期限非常有限，我们非常感谢您的帮助。我们在创建初始查询方面有一些帮助，如下所示：
SELECT equipment.name，
equipment.new_modelno，
equipment.new_serialno，
equipment.new_type，
equipment.new_make，
equipment.new_mast，
equipment .new_capacity，
activitypartycustomer.partyidname，
serviceappointment.scheduledstart，
serviceappointment.scheduledend
来自FilteredEquipment AS设备
INNER JOIN FilteredActivityParty AS活动派对
ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer
ON serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participatio ntypemask = 11
WHERE（serviceappointment.statecode = 0）
ORDER BY equipment.name
此查询完全给了我们我们想要的信息但是我们注意到已经安排多次的资源在第二行的报告上出现两次。
我们希望资源一次列出，并且额外的服务活动详细信息显示在报告的其他列中。
For例如，我们将拥有设备名称，设备型号，设备品牌，设备序列号，服务活动1的客户名称，服务活动1的日期，服务活动的客户名称2以及服务活动的日期2.所有这些字段都会出现因为列和完整的细节不会在第二行重复。
例如
设备名称设备SerialNo客户开始日期结束日期
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS
EquipName EquipSerialNo Cust开始日期结束日期Cust开始日期结束日期
E15,3232，ABC Shoes，5-01-2007,30-06-2007， ABC Shoes，1-08-2007,30-04-2007
这可能实现吗？我们可以用某种方式编辑查询给我们
所需的信息吗？
真的很感激任何帮助。 。
非常感谢
马克
是的，但这远不是微不足道的。如果您使用SQL Server 2005，则可以使用PIVOT功能（http://technet.microsoft.com/en-us/library/ms177410.aspx）;无论哪种方式，以下页面都值得一读：http：//www.mssqltips.com/tip.asp？tt = 937
是它远非微不足道。如果您使用SQL Server 2005，则可以使用PIVOT功能（http://technet.microsoft.com/en-us/library/ms177410.aspx）;以下两页值得一读：http：//www.mssqltips.com/tip.asp？tt = 937
嗨
非常感谢您的回复。至少我知道如何做到这一点。我们编写SQL查询的经验非常有限，似乎我们必须编写一个非常复杂的查询来获得我们需要的结果。我写了以下查询：
SELECT< equipment.name>
partyidname AS Customer
scheduledstart AS Scheduled Start
scheduledend AS预定结束
FROM
（equipment.name）AS Sourcetable
FROM
SELECT equipment.name，
equipment.new_modelno，
equipment.new_serialno，
equipment.new_type，
equipment.new_make，
equipment.new_mast，
equipment.new_capacity，
activitypartycustomer.partyidname，
serviceappointment .scheduledstart，
serviceappointment.scheduledend
来自FilteredEquipment AS设备
INNER JOIN FilteredActivityParty AS activityparty
ON设备。 equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer ON
serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participationtypemask = 11
AS来源表
PIVOT
（设备名称）
IN（partyidname，
scheduledstart，
scheduledend）
AS
资料来源
我将很快测试这个查询，我非常怀疑它将会有一个百分之百的工作。
你有什么东西可以立即注意到这个查询有问题。
谢谢
您好我不能保证它会起作用，因为我无法测试此查询。此查询只有在你有不超过2个活动的情况下才有效，因为假设有两个我首先选择MIN并且最后选择MAX，这与MIN不同，MIN之间的任何内容都不会出现在这种类型的查询中，如果你认为你有两个以上的客户坚持你的原始查询的情况，否则它将需要认真的编程，如循环数据和旋转它。
[PHP]
SELECT name，new_modelno，new_serialno，new_type，new_make，new_mast，new_capacity，
（从FilteredActivityParty中选择partyidname，其中activityid = Activity1 AND activitypartycustomer.participationtypemask = 11）partyidname1，
（从FilteredServiceAppointment中选择scheduledstart，其中activityid = Activity1）scheduledstart1，
（选择scheduledend from FilteredServiceAppointment其中activityid = Activity1）scheduledend1，
（从FilteredActivityParty中选择partyidname，其中activityid = Activity2 AND Activity2<> Activity1 AND activitypartycustomer.participationtypemask = 11）partyidname2，
（从FilteredServiceAppointment中选择scheduledstart，其中activityid = Activity2 AND Activity2<> Activity1）scheduledstart2，
（从FilteredServiceAppointment中选择scheduledend，其中activityid = Activity2 AND Activity2<> Activity1）scheduledend2，
FR OM（
SELECT equipment.name name，
equipment.new_modelno new_modelno，
equipment.new_serialno new_serialno，
equipment.new_type new_type，
equipment.new_make new_make，
equipment.new_mast new_mast，
equipment.new_capacity new_capacity，
min（serviceappointment.activityid）Activity1，
max（serviceappointment.activityid）活动2
来自FilteredEquipment AS设备
INNER JOIN FilteredActivityParty AS活动派对ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid
WHERE（serviceappointment.statecode = 0）
group by equipment.name，
equipment.new_modelno，
equipment.new_serialno，
equipment.new_type，
equipment.new_make，
equipment.new_mast，
equipment.new_capacity）a
ORDER BY equipment.name [/ PHP]
祝你好运。
Hi
We are attempting to create a dynamic spreadsheet which will extract information from CRM / SQL.
We have very limited expierence in creating SQL queries and we would really appreciate any assistance. We had some assistance in creating an initial query which looked as follows:
SELECT equipment.name ,
equipment.new_modelno,
equipment.new_serialno ,
equipment.new_type ,
equipment.new_make,
equipment.new_mast,
equipment.new_capacity,
activitypartycustomer.partyidname,
serviceappointment.scheduledstart,
serviceappointment.scheduledend
FROM FilteredEquipment AS equipment
INNER JOIN FilteredActivityParty AS activityparty
ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer
ON serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participationtypemask = 11
WHERE (serviceappointment.statecode = 0)
ORDER BY equipment.name
This query gave us exactly the information we wanted however we noticed that a resource which has been scheduled more than once appears twice on the report on a second row.
We would prefer for the resource to be listed once and the additonal service activity details to appear in additional columns on the report.
For example, we would have equipment name, equipment model, equipment make, equipment serial number, customer name for service activity 1, dates for service activity 1, customer name for service activity 2 and the dates for service activity 2. All these fields would appear as columns and the full detail would not be repeated in a second row.
For example
Equipment Name Equipment SerialNo Customer Start Date End Date
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS
EquipName EquipSerialNo Cust Start Date End Date Cust Start Date End Date
E15,3232,ABC Shoes,5-01-2007,30-06-2007,ABC Shoes,1-08-2007,30-04-2007
Is this possible to achieve? Can we edit the query in some way to give us
the required information?
Really appreciate any assistance. .
Thanks alot
Mark
Hi
We are attempting to create a dynamic spreadsheet which will extract information from CRM / SQL.
We have very limited expierence in creating SQL queries and we would really appreciate any assistance. We had some assistance in creating an initial query which looked as follows:
SELECT equipment.name ,
equipment.new_modelno,
equipment.new_serialno ,
equipment.new_type ,
equipment.new_make,
equipment.new_mast,
equipment.new_capacity,
activitypartycustomer.partyidname,
serviceappointment.scheduledstart,
serviceappointment.scheduledend
FROM FilteredEquipment AS equipment
INNER JOIN FilteredActivityParty AS activityparty
ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer
ON serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participationtypemask = 11
WHERE (serviceappointment.statecode = 0)
ORDER BY equipment.name
This query gave us exactly the information we wanted however we noticed that a resource which has been scheduled more than once appears twice on the report on a second row.
We would prefer for the resource to be listed once and the additonal service activity details to appear in additional columns on the report.
For example, we would have equipment name, equipment model, equipment make, equipment serial number, customer name for service activity 1, dates for service activity 1, customer name for service activity 2 and the dates for service activity 2. All these fields would appear as columns and the full detail would not be repeated in a second row.
For example
Equipment Name Equipment SerialNo Customer Start Date End Date
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS
EquipName EquipSerialNo Cust Start Date End Date Cust Start Date End Date
E15,3232,ABC Shoes,5-01-2007,30-06-2007,ABC Shoes,1-08-2007,30-04-2007
Is this possible to achieve? Can we edit the query in some way to give us
the required information?
Really appreciate any assistance. .
Thanks alot
MarkYes it is but far from trivial. If you use SQL Server 2005 you can use the PIVOT functionality ( http://technet.microsoft.com/en-us/library/ms177410.aspx ); either way the following page is worth reading: http://www.mssqltips.com/tip.asp?tip=937
Yes it is but far from trivial. If you use SQL Server 2005 you can use the PIVOT functionality ( http://technet.microsoft.com/en-us/library/ms177410.aspx ); either way the following page is worth reading: http://www.mssqltips.com/tip.asp?tip=937Hi
Thanks alot for your reply. As least I have an idea as to how this can be done. We have very limited experience in writing SQL Queries and it seems as though we have to write quite a complex query to get the results we need. I have written the following query:
SELECT <equipment.name>
partyidname AS Customer
scheduledstart AS Scheduled Start
scheduledend AS Scheduled End
FROM
(equipment.name) AS Sourcetable
FROM
SELECT equipment.name ,
equipment.new_modelno,
equipment.new_serialno ,
equipment.new_type ,
equipment.new_make,
equipment.new_mast,
equipment.new_capacity,
activitypartycustomer.partyidname,
serviceappointment.scheduledstart,
serviceappointment.scheduledend
FROM FilteredEquipment AS equipment
INNER JOIN FilteredActivityParty AS activityparty
ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS
serviceappointment
ON serviceappointment.activityid =
activityparty.activityid
INNER JOIN FilteredActivityParty AS activitypartycustomer ON
serviceappointment.activityid =
activitypartycustomer.activityid AND
activitypartycustomer.participationtypemask = 11
AS Sourcetable
PIVOT
(equipment.name)
IN (partyidname,
scheduledstart,
scheduledend)
AS
Sourcetable
I will be testing this query shortly and I very much doubt that it is going to work one hundered percent.
Is there anything that you notice immediately that is wrong with this query.
Thanks
Hello I don?t give you a guaranty that it would work because I have no way of testing this query. Also this query would work only if you have not more than 2 activities because with assumption of two I go for first selecting MIN and last selecting MAX which is not the same as MIN nothing in between will be available in this type of query and if you think that you have cases where you have more then 2 customers stick to your original query otherwise it will need serious programming like looping through the data and pivoting it.
[PHP]
SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivityParty where activityid = Activity1 AND activitypartycustomer.participationtypemask = 11) partyidname1,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity1) scheduledstart1,
(select scheduledend from FilteredServiceAppointment where activityid = Activity1) scheduledend1,
(select partyidname from FilteredActivityParty where activityid = Activity2 AND Activity2 <> Activity1 AND activitypartycustomer.participationtypemask = 11) partyidname2,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2,
(select scheduledend from FilteredServiceAppointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2,
FROM (
SELECT equipment.name name ,
equipment.new_modelno new_modelno,
equipment.new_serialno new_serialno,
equipment.new_type new_type,
equipment.new_make new_make,
equipment.new_mast new_mast,
equipment.new_capacity new_capacity,
min(serviceappointment.activityid) Activity1,
max(serviceappointment.activityid) Activity2
FROM FilteredEquipment AS equipment
INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid
INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid
WHERE (serviceappointment.statecode = 0)
group by equipment.name ,
equipment.new_modelno,
equipment.new_serialno ,
equipment.new_type ,
equipment.new_make,
equipment.new_mast,
equipment.new_capacity) a
ORDER BY equipment.name [/PHP]
Good Luck.
这篇关于编写查询以更改数据的呈现方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
