首页 \ 问答 \ SQL查询PIVOT到MS Access SQL查询(SQL Query PIVOT to MS Access SQL Query)

SQL查询PIVOT到MS Access SQL查询(SQL Query PIVOT to MS Access SQL Query)

我在SQL Server上有这个查询

    ;WITH tmpTbl AS
    (SELECT Kit_Tbl.Kit_Number
           ,Kit_Tbl.Kit_Refrigerant
           ,CompType_Tbl.Component_Type
           ,Comp_List_Tbl.Component_Num
            FROM Kit_Tbl
    INNER JOIN Kit_Library
    ON Kit_Library.Kit_Number = Kit_Tbl.Kit_Number
    INNER JOIN CompType_Tbl
    ON CompType_Tbl.Component_Type = Kit_Library.Component_Type
    INNER JOIN Comp_List_Tbl
    ON Comp_List_Tbl.Component_Type = CompType_Tbl.Component_Type)

    select Kit_Number
         , Kit_Refrigerant
         , [Compressor]
         , [Condensing Unit]
    from
    (
      select Kit_Number, Component_Type, Component_Num, Kit_Refrigerant
      from tmpTbl
    ) d
    pivot
    (
      max(Component_Num)
      for Component_Type in ([Compressor], [Condensing Unit])
    ) piv;

我尝试将其转换为MS Access查询,但我在Transform语句上遇到语法错误:

    TRANSFORM MAX(Comp_List_Tbl.Component_Num) AS Comp_Num
    SELECT Kit_Tbl.Kit_Number,
    CompType_Tbl.Component_Type,MAX(Comp_List_Tbl.Component_Num)
    FROM Comp_List_Tbl INNER JOIN (Kit_Tbl INNER JOIN (Kit_Library INNER JOIN
    CompType_Tbl ON Kit_Library.Component_Type = CompType_Tbl.Component_Type) ON 
    Kit_Tbl.Kit_Number = Kit_Library.Kit_Number) ON (CompType_Tbl.Component_Type = 
    Comp_List_Tbl.Component_Type);
    GROUP BY Kit_Tbl.Kit_Number 
    PIVOT IN CompType_Tbl.Component_Type

谁能帮我这个?


I have this query on SQL Server

    ;WITH tmpTbl AS
    (SELECT Kit_Tbl.Kit_Number
           ,Kit_Tbl.Kit_Refrigerant
           ,CompType_Tbl.Component_Type
           ,Comp_List_Tbl.Component_Num
            FROM Kit_Tbl
    INNER JOIN Kit_Library
    ON Kit_Library.Kit_Number = Kit_Tbl.Kit_Number
    INNER JOIN CompType_Tbl
    ON CompType_Tbl.Component_Type = Kit_Library.Component_Type
    INNER JOIN Comp_List_Tbl
    ON Comp_List_Tbl.Component_Type = CompType_Tbl.Component_Type)

    select Kit_Number
         , Kit_Refrigerant
         , [Compressor]
         , [Condensing Unit]
    from
    (
      select Kit_Number, Component_Type, Component_Num, Kit_Refrigerant
      from tmpTbl
    ) d
    pivot
    (
      max(Component_Num)
      for Component_Type in ([Compressor], [Condensing Unit])
    ) piv;

I tried converting it to MS Access query but I encountered Syntax Error on Transform Statement:

    TRANSFORM MAX(Comp_List_Tbl.Component_Num) AS Comp_Num
    SELECT Kit_Tbl.Kit_Number,
    CompType_Tbl.Component_Type,MAX(Comp_List_Tbl.Component_Num)
    FROM Comp_List_Tbl INNER JOIN (Kit_Tbl INNER JOIN (Kit_Library INNER JOIN
    CompType_Tbl ON Kit_Library.Component_Type = CompType_Tbl.Component_Type) ON 
    Kit_Tbl.Kit_Number = Kit_Library.Kit_Number) ON (CompType_Tbl.Component_Type = 
    Comp_List_Tbl.Component_Type);
    GROUP BY Kit_Tbl.Kit_Number 
    PIVOT IN CompType_Tbl.Component_Type

Can anyone help me with this?


原文:https://stackoverflow.com/questions/24667082
更新时间:2020-03-26 12:20

最满意答案

在你的最后一行:

PIVOT CompType_Tbl.Component_Type

不需要IN


In your last line :

PIVOT CompType_Tbl.Component_Type

No IN is required.

2014-07-10

相关文章

更多

最新问答

更多
  • css在元素之前中断列而不破坏包装器(css break column before element without breaking the wrapper)
  • 如何在Xamarin共享项目中使用自定义渲染器(How to use Custom Renderer in Xamarin Shared Project)
  • 如何为特定表中的特定字段设置唯一?(How to set unique for specific field from specific table?)
  • Google SDK iOS - sign()方法完成处理程序(Google SDK iOS - sign() method completion handler)
  • 在具有接口{}值的地图上实现String()(Implement String() on a map with interface{} values)
  • 检查数据库中是否已存在用户名(Check if username already exist in DB)
  • 使用javascript进行ajax调用时阻止用户交互(Block user interaction while doing ajax call using javascript)
  • 什么'if(err)'在Javascript中精确测试?(What does 'if (err)' tests precisely in Javascript?)
  • jQuery mouseleave无法正常工作(jQuery mouseleave not working)
  • 寻求使用的一些说明(Seeking some clarification on use of )
  • 将数组传递给注释的语法(syntax for passing array to annotation)
  • 用于从两个日期范围之间的文件中提取数据的Shell脚本(Shell script to extract data from file between two date ranges)
  • 元素隐藏但父()没有(Element hides but parent() not)
  • 如何使用Google App Engine Java平台开发web ui(How to develop web ui with Google App Engine Java platform)
  • 对于OWL A级;(For an OWL class A; Getting all properties that A is their domain)
  • Excel VBA公式格式问题(Excel VBA Formula Format Issue)
  • ORA - 02287序列号不允许在这里(ORA - 02287 sequence number not allowed here)
  • Github拉忽略特定文件(Github Pull Ignore Specific File)
  • SQL CONVERT函数在SQL Server中工作但不在应用程序中(SQL CONVERT function working in SQL Server but not in application)
  • backbone.js适用于大型应用程序(backbone.js for large applications)
  • 防止程序关闭(Preventing program from closing)
  • 生成不带图像的heightMap(Generating a heightMap without an Image)
  • Bootstrap - 如何将包含文本的div居中?(Bootstrap - How to center div that has text inside it?)
  • Android - 片段findViewById()总是null?(Android - Fragment findViewById() always null?)
  • 确定CSS中的高度(Figuring out heights in CSS)
  • 使用__autoload包含类和使用命名空间(Use __autoload to include class and use namespace)
  • setTimeout()不允许我传递文本值[重复](setTimeout() doesn't allow me to pass text values [duplicate])
  • 在NSUserDefault中恢复值(Restoring value in NSUserDefault)
  • 知道如何将这种下沉的悬停效果添加到图像/链接吗?(Any idea how to add this sinking hover effect to an image/link?)
  • 在XIB中淡入/淡出UISegmentedControl(fade in/fade out UISegmentedControl in XIB)