SQL Query PIVOT to MS Access SQL Query

Question

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?

parakmiakos · Accepted Answer · 2014-07-10 08:33:07Z

1

In your last line :

PIVOT CompType_Tbl.Component_Type

No IN is required.

answered Jul 10, 2014 at 8:33

parakmiakos

3,0209 gold badges31 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query PIVOT to MS Access SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related