Transform and Pivot in Access 2013 SQL

Access 2013: Pivot multiple columns

TRANSFORM can only occur at the start of an SQL statement.

First unpivot all the years and only then pivot the metric. Now, as MS Access apparently
has its limits when it comes to complex queries (many unions), it is best to pass via
an intermediate table:

SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 1999 As Year, [1999] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2000 As Year, [2000] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2001 As Year, [2001] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2002 As Year, [2002] As Value
            FROM        DataTable
        )
INTO myTempTable;

I have union-ed 4 years, but you should check how far you can go before MS Access refuses the statement for being too complex.
Then, add some more years, with the same number of unions:

INSERT INTO myTempTable (FileIDRunID, Metric, Year, Value)
SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 2003 As Year, [2003] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2004 As Year, [2004] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2005 As Year, [2005] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2006 As Year, [2006] As Value
            FROM        DataTable
        );

etc... to finally do this:

TRANSFORM FIRST(Value)
SELECT   FileIDRunID,
         Year
FROM     myTempTable
GROUP BY FileIDRunID,
         Year
PIVOT    Metric

And then drop the temporary table and compress the database to get the original size back. If possible, redesign the database to use the completely unpivoted structure as in the temporary table.

Access SQL With PIVOT

The syntax to pivot in MS Access is different from sql server. You have to use the TRANSFORM function to pivot data.

The syntax will be similar to this:

TRANSFORM SUM([Retention & Graduation].Hc)
SELECT [Retention & Graduation].Year, 
  [Retention & Graduation].Status, 
  [Retention & Graduation].Hc
FROM [Retention & Graduation]
GROUP BY [Retention & Graduation].Year
PIVOT [Retention & Graduation].[STATUS]

Pivoting data in MS Access

You can pivot the data using TRANSFORM:

TRANSFORM COUNT(MenuItems.MealType)
SELECT April2013.SID, MenuItems.MealType
FROM April2013 
LEFT JOIN MenuItems 
  ON MenuItems.Item=April2013.Item
GROUP BY April2013.SID
PIVOT MenuItems.MealType; 

How to Pivot Query Results in MS Access?

Simply use the crosstab query, a unique command in Jet/ACE SQL:

TRANSFORM Max(t.rights) AS MaxOfrights
SELECT t.user
FROM accTable t
GROUP BY t.user
PIVOT t.report;

Transform and Pivot in Access 2016

First I tried this.

SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q01' as Question, [Q01] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q02' as Question, [Q02] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q03' as Question, [Q03] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q04' as Question, [Q04] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q05' as Question, [Q05] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q06' as Question, [Q06] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q07' as Question, [Q07] as Response FROM Random_data_generator
ORDER BY StudentID, Question;

But I couldn't figure out how to do it dynamically for a random number of answers. So I blew the dust off my VB books and ended up with this which I'm sure is poorly coded and will make folks wince. But does work.

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef

ColumnCount = CurrentDb.TableDefs("Random_data_generator").Fields.Count
ColumnCount2 = ColumnCount - 2
Dim QueryString As String
Dim QueryEntry As String
Dim counter As Integer
Dim counterEntry As String
Dim QueryTest As Recordset
Dim QuestionnaireNum As String
counter = 1


Do While counter <= ColumnCount2

 counterEntry = Format(counter, "00")

    QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator UNION ALL " & vbCrLf
    counter = counter + 1
    QueryEntry = QueryEntry + QueryString

 Loop

 counterEntry = Format(counter, "00")

 QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator ORDER BY StudentID, Question;"
 QueryEntry = QueryEntry + QueryString

 On Error Resume Next
 DoCmd.DeleteObject acQuery, "tempQry"
 On Error GoTo 0

 Set qdf = db.CreateQueryDef("tempQry", QueryEntry)

 DoCmd.OpenQuery ("tempQry")

Add a total column in an access transform statement

If you had used the Query Wizard it should have built it for you by default, unless you have checked it not to include "Totals" column. However you can manually add it, by going to the design view and adding this,

TRANSFORM 
    Sum(revenue) AS sum_revenue 
SELECT 
    [year], 
    Sum(revenue) As [Total Of SumRevenue]
FROM 
    revenues 
GROUP BY 
    [year]
PIVOT 
    [month];

PS: I played with the query, I get the right result. Not sure why yours is not !

Convert Access TRANSFORM/PIVOT query to SQL Server

Here is the equivalent version using the PIVOT table operator:

SELECT *
FROM
(
  SELECT 
    CASE 
      WHEN sumUnits > 0 
      THEN SumAvgRent / sumUnits ELSE 0 
  END AS Expr1,
  Description,
  Period
  FROM temp
) t
PIVOT
(
  AVG(Expr1)
  FOR Period IN(Period1, Period2, Period3)
) p;

SQL Fiddle Demo

For instance, this will give you:

| DESCRIPTION | PERIOD1 | PERIOD2 | PERIOD3 |
---------------------------------------------
|          D1 |      10 |       0 |      20 |
|          D2 |     100 |    1000 |       0 |
|          D3 |      50 |      10 |       2 |

---

Note that When using the MS SQL Server PIVOT table operator, you have to enter the values for the pivoted column. However, IN MS Access, This was the work that TRANSFORM with PIVOT do, which is getting the values of the pivoted column dynamically. In this case you have to do this dynamically with the PIVOT operator, like so:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(Period)
                FROM temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');



SET @query =  ' SELECT Description, ' + @cols + '
    FROM 
    (
      SELECT 
        CASE 
          WHEN sumUnits > 0 
          THEN SumAvgRent / sumUnits ELSE 0 
      END AS Expr1,
      Description,
      Period
      FROM temp
    ) t
    PIVOT
    (
      AVG(Expr1)
      FOR Period IN( ' + @cols + ') 
    ) p ';



Execute(@query);

Updated SQL Fiddle Demo

This should give you the same result:

| DESCRIPTION | PERIOD1 | PERIOD2 | PERIOD3 |
---------------------------------------------
|          D1 |      10 |       0 |      20 |
|          D2 |     100 |    1000 |       0 |
|          D3 |      50 |      10 |       2 |

---

Related Topics