在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屋！