在sql server中的透视示例 [英] Pivoting example in sql server
问题描述
i使用旋转函数编写查询语法是正确的,但它返回''附近的错误语法'('
与cte as(
选择mileStoneName,siteid,ActualDate
来自st_sitemilestones A,st_genericmilestones B,Site C
其中A.milestoneid = B.milestoneid
AND A.Siteid = C.Siteid和siteid ='IN-1001977'
)< br $>
CTE输出:
mileStoneName siteid ActualDate
SRN关闭IN-1001977 2011-06-08 11:43:56.877
PO收盘IN-1001977 2011-06-08 11:43:58.080
F&F IN-1001977 2011-06-08 11:51:48.407
财务IN-1001977 2011-06-08 11:50:21.423
终止IN-1001977 2011-06 -08 11:48:27.860
EB IN-1001977 2011-06-08 11:53:26.267
i有以下查询并获得不正确'('
select * from cte
pivot(
MIN(ActualDate)FOR mileStoneName IN ([SRN关闭],[PO关闭],[F&F],
[财务],[终止],[EB]))
AS Actual_Date
我所需的输出:
siteid SRN已关闭PO关闭F&F
IN-1001977 2011-06-08 11:43:56.877 2011-06-08 11:43: 56.877 2011-06-08 11:43:56.877 56.877
i have written the query using pivoting function syntax is correct but it is returning Incorrect syntax near '('
with cte as (
Select mileStoneName,siteid, ActualDate
from st_sitemilestones A, st_genericmilestones B, Site C
where A.milestoneid = B.milestoneid
AND A.Siteid = C.Siteid and siteid ='IN-1001977'
)
CTE Outputis :
mileStoneName siteid ActualDate
SRN closed IN-1001977 2011-06-08 11:43:56.877
PO closed IN-1001977 2011-06-08 11:43:58.080
F&F IN-1001977 2011-06-08 11:51:48.407
Finance IN-1001977 2011-06-08 11:50:21.423
Termination IN-1001977 2011-06-08 11:48:27.860
EB IN-1001977 2011-06-08 11:53:26.267
i have wittren the following query and getting Incorrect syntax near '('
select * from cte
pivot (
MIN(ActualDate) FOR mileStoneName IN ([SRN closed],[PO closed],[F&F],
[Finance],[Termination],[EB]))
AS Actual_Date
my required output :
siteid SRN closed PO closed F&F
IN-1001977 2011-06-08 11:43:56.877 2011-06-08 11:43:56.877 2011-06-08 11:43:56.877 56.877
推荐答案
在您的CTE中做一些修改
do some modification in your CTE
with cte (mileStoneName,Indussiteid,ActualDate)
AS
(
Select mileStoneName,Indussiteid, ActualDate
from st_sitemilestones A, st_genericmilestones B, Site C
where A.milestoneid = B.milestoneid
AND A.Siteid = C.Siteid and siteid ='IN-1001977'
)
select * from cte
pivot (
MIN(ActualDate) FOR mileStoneName IN ([SRN closed],[PO closed],[F&F],
[Finance],[Termination],[EB]))
AS Actual_Date
并且一起运行
and run all together
这篇关于在sql server中的透视示例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!