Error converting TRANSFORM to PIVOT Statement

Question

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

@bluefeet This is the error message " Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'FOR'. " . the comma was by an error while posting. — WiredTheories, Dec 10 '14 at 14:52

Taryn · Accepted Answer · 2014-12-10T15:10:45.837

2

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.

edited Dec 10 '14 at 15:10

answered Dec 10 '14 at 14:52

Taryn

242,637
56
362
405

Thanks for pointing out the error. Also I had to include the column [text_status] in the select statement and it worked fine. I had a question for now in the [text_status] which is present in the table1 there is just APP. So should I remove the other values ? Also table1 is view formed on certain condition. So in future the values in [text_status] may change. – WiredTheories Dec 10 '14 at 15:09
@Lewan If you only have `APP` now then you can remove the others and add more once you get values in the future. In SQL Server the pivot function works slightly different because you have to know the values which will be columns ahead of time - it doesn't create it automatically. If the values will be unknown, then you will need to look at using dynamic SQL - take a look at my answer here which shows a variety of ways to pivot in SQL server including dynamic - http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Taryn Dec 10 '14 at 15:12
for the current scenario I'm using a view so I would remove the rest of the values. For Dynamic I have to use it in SP. The view approach fits my needs perfectly. I appreciate your help a lot :) – WiredTheories Dec 10 '14 at 15:21
I miss the column CountOftext in the results , also i need to rename the column to totalCountOftext. Could you let me know why it is getting missed and could I rename it in the Pivot statement ? – WiredTheories Dec 10 '14 at 15:39
@Lewan `CountOfText` is the being aggregated so it doesn't exist in the final select. The total count is what exists under each column pivoted. If you need it then you would need to include the column twice - once for the pivot and once for the final select (but they would need different names). Do you need a totals column? – Taryn Dec 10 '14 at 15:43
Yes I need the total column since this Pivoted table is further used as a check where the totals column is checked on business condition. – WiredTheories Dec 10 '14 at 15:51
@Lewan Ok, so that's a different question, can you post a new question with some sample data from your table and the final desired result. – Taryn Dec 10 '14 at 15:53
I have posted the final desired result. Also the Access Transform statement is the same. Sum([TABLE1].CountOftext) AS [Total Of CountOftext] is mentioned in it. – WiredTheories Dec 10 '14 at 16:09
Yes that is exactly what I needed. [Total Of CountOftext] looks perfect !! – WiredTheories Dec 10 '14 at 16:22
I appreciate all your help in this. – WiredTheories Dec 10 '14 at 16:26
I will ask another question unable to post it – WiredTheories Dec 19 '14 at 11:34
@Lewan If you have a new question, then [ask a new one](http://stackoverflow.com/questions/ask). – Taryn Dec 19 '14 at 11:44
It was related to using pivot dynamically. I used your code http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server. But i receive an error **Msg 8156, Level 16, State 1, Line 14 The column 'Factory' was specified multiple times for 'p'**. I will compose a different question. You are fast responding to it. Appreicate. – WiredTheories Dec 19 '14 at 11:47

Error converting TRANSFORM to PIVOT Statement

1 Answers1