Pivot function conversion from Oracle to T-Sql

Question

I having a problem properly converting below sql statement from Oracle to SQL Server.
Error: Incorrect syntax near '67'.

SELECT
  PROD_ID,
  RDS_SECTOR,
  RDS_SUB_SECTOR,
  RDS_CATEGORY,
  RDS_SEGMENT,
  RDS_BRAND,
  RDS_SUB_BRAND,
  RDS_FORM_GLOBL
FROM
  (
    select
      PROD_ID,
      PROD_ATTR_TYPE_ID,
      ATTR_VAL
    from
      [BSR_STG_Q].[Cdl_Prod_Attr_Dim] ad
    where
      CURR_IND = 'Y'
      AND PROD_ATTR_END_DATE = '9999-12-31 00:00:00'
  ) pv PIVOT (
    max(ATTR_VAL) for PROD_ATTR_TYPE_ID in (
      67 AS RDS_SECTOR,
      108 AS RDS_SUB_SECTOR,
      63 AS RDS_CATEGORY,
      61 AS RDS_SEGMENT,
      66 AS RDS_BRAND,
      418 AS RDS_SUB_BRAND,
      65 AS RDS_FORM_GLOBL
    )
  )

I glad if someone could help

Answer 1

     SELECT
       PROD_ID,
           [67] AS RDS_SECTOR,
           [108] AS RDS_SUB_SECTOR,
           [63] AS RDS_CATEGORY,
           [61] AS RDS_SEGMENT,
           [66] AS RDS_BRAND,
           [418] AS RDS_SUB_BRAND,
           [65] AS RDS_FORM_GLOBL
     FROM
       (
         select
           PROD_ID,
           PROD_ATTR_TYPE_ID,
           ATTR_VAL
         from
           [BSR_STG_Q].[Cdl_Prod_Attr_Dim] ad
         where
           CURR_IND = 'Y'
           AND PROD_ATTR_END_DATE = '9999-12-31 00:00:00'
       ) pv PIVOT (
         max(ATTR_VAL) for PROD_ATTR_TYPE_ID in (
           [67],
           [108],
           [63],
           [61],
           [66],
           [418],
           [65]
         )
       ) pvt

Tom

Answer 2

Hi @BartoszWachocki-4076,

Thank you so much for posting here.

Tom already provided the correct answer.

You could refer more details and examples from below article:
Using PIVOT and UNPIVOT

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

Looks good to me. Thanks a lot!