CODEWITHSUNDEEP
sql-serverms-accesspivot
WiredTheories
WiredTheories

Reputation: 231

Error converting TRANSFORM to PIVOT Statement

I have the following statement in MS Access:

TRANSFORM Sum([TABLE1].CountOftext) AS SumOfCountOftext
SELECT 
  [TABLE1].[ID], 
  [TABLE1].FOS, 
  [TABLE1].VTCD, 
  Sum([TABLE1].CountOftext) AS [Total Of CountOftext]
FROM [TABLE1]
GROUP BY [TABLE1].[ID], [TABLE1].FOS, [TABLE1].VTCD
PIVOT [TABLE1].text_status;

My table is TABLE1 and it looks like:

+--------------+-----+--------+------+-------------+-------------+
|      ID      | FOS |   MD   | VTCD | TEXT_STATUS | COUNTOFTEXT |
+--------------+-----+--------+------+-------------+-------------+
| 822234361802 | PS4 | 10X10F | XTAP | APP         |           1 |
| 822234361802 | PS4 | 10X10F | XTBP | APP         |           1 |
| 822234361802 | PS4 | 10X10F | XTFP | APP         |           1 |
| 822234361802 | PS4 | 10X10F | XTPD | APP         |           1 |
| 822234361802 | PS4 | 10X10F | XTPL | APP         |           1 |
+--------------+-----+--------+------+-------------+-------------+

I have gone through some posts and tried to convert but I still get an error, below is the converted query in T-SQL

SELECT *
FROM
(
    SELECT 
      Sum([TABLE1].CountOftext) AS [Total Of CountOftext] ,
      [TABLE1].[ID], 
      [TABLE1].FOS, 
      [TABLE1].VTCD
    FROM [TABLE1]
) T
PIVOT ( T.[Total Of CountOftext]
        FOR T.[text_status] IN (APP, CRE, ORD, REQ, TBA, TBT, WRK )
        ) P

The error that I'm getting is:

Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'FOR'.

Could someone help what I'm doing wrong.

The final result needed.

     ID       FOS VTCD  Total Of CountOftext    APP 

 822234361802  PS4 XTAP         1               1    
 822234361802  PS4 XTBP         1               1     
 822234361802  PS4 XTFP         1               1     
 822234361802  PS4 XTPD         1               1     
 822234361802  PS4 XTPL         1               1

Upvotes: 0

Views: 54

Answers (1)

Taryn
Taryn

Reputation: 247720

Your syntax is close but you don't need the SUM in the inner query, you should be able to use:

SELECT *
FROM
(
    SELECT
      [TABLE1].CountOftext ,
      [TABLE1].[ID], 
      [TABLE1].FOS, 
      [TABLE1].VTCD,
      [TABLE1].text_status
    FROM [TABLE1]
) T
PIVOT 
(
  sum(T.CountOftext)
  FOR T.[text_status] IN (APP, CRE, ORD, REQ, TBA, TBT, WRK )
) P

The aggregate function goes in the PIVOT part of the query, not inside your subquery.

Upvotes: 2

Related Questions