Convert Access Transform/Pivot Query to SQL Server

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 |

Convert MS Access TRANSFORM/PIVOT query to SQL Server

I have such universal stored procedure for pivoting 

CREATE PROCEDURE [dbo].[msrTransformAsAccessDo]
    @TRANSFORM_Function nvarchar(max),
    @TRANSFORM_Field nvarchar(max),
    @SQL_SELECT nvarchar(max),
    @SQL_FROM_WHERE nvarchar(max),
    @SQL_GROUPBY_HAVING_ORDERBY nvarchar(max),
    @PIVOTBY nvarchar(max) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @DynSQL nvarchar(max)

    SET @DynSQL = N' SELECT DISTINCT ' + @PIVOTBY + ' as key_value into ##pivoting ' + @SQL_FROM_WHERE + ' ORDER BY ' + @PIVOTBY

    DROP TABLE IF EXISTS ##pivoting
    EXEC (@DynSQL)

    DECLARE @Values nvarchar(max) = N''
    DECLARE @tmpStr nvarchar(max)
    DECLARE @rsk CURSOR         

    SET @rsk = CURSOR SCROLL
        FOR 
            select key_value from ##pivoting
    OPEN @rsk         
        FETCH NEXT FROM @rsk INTO @tmpStr
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @Values = @Values + ', ' + @TRANSFORM_Function + '(Case When convert(nvarchar,' + @PIVOTBY + ')=convert(nvarchar,''' + @tmpStr + ''') Then ' + @TRANSFORM_Field + ' Else Null End) AS [' + @tmpStr + '] '
                FETCH NEXT FROM @rsk INTO @tmpStr
            END
    CLOSE @rsk

    DROP TABLE IF EXISTS ##pivoting

    SET @DynSQL = @SQL_SELECT + ' ' + @Values + ' ' + @SQL_FROM_WHERE + ' ' + @SQL_GROUPBY_HAVING_ORDERBY

    EXEC (@DynSQL)
END
GO

And call for your case:

EXECUTE msrTransformAsAccessDo 
   'Avg', 
   '[X Avg Sub Group]', 
   'SELECT Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl, Avg([X Avg Sub Group]) AS [Total Of X Avg Sub Group] ', 
   'FROM [Capability Data with Shift] WHERE (((Process)=''BBB WELDING'')) ', 
   'GROUP BY Day, Process, PARTNO_VAL0, CTQNO_VAL0, ctq_description, MACHINE_VAL0, usl, lsl ', 
   'SHIFT_VAL0'

Convert Access TRANSFORM/PIVOT query to SQL Server 2012

Well I figured it out myself. I am not sure if this will help others but here is the final query that will work in SQL Server. I converted this from MS Access:

SELECT pt.* FROM 
(SELECT SUM(TTQTY * qty) AS quantity, matlprodcode AS product_code, cvend_name AS supplier, CITMCD as material, matldesc AS description, MIN(unit_cost) AS unit_cost, budgetmonth 
FROM acs_Budget 
WHERE matlprodcode NOT LIKE ';I8*' 
AND matlprodcode NOT LIKE 'I1' 
GROUP BY matlprodcode, cvend_name, CITMCD, matldesc, budgetmonth) AS src 
PIVOT (MIN(quantity) FOR src.budgetmonth IN ([4/1/2015], [5/1/2015], [6/1/2015], [7/1/2015], [8/1/2015], [9/1/2015], [10/1/2015], [11/1/2015], [12/1/2015], [1/1/2016], [2/1/2016], [3/1/2016])) AS pt;

Some things that helped me:

  • First I selected everything that I wanted to see. Including what MS Access was transforming and pivoting.
  • Then I found out what each column MS Access was pivoting, in this case the quantity.
  • So I did a MIN of the quantity because the PIVOT needs an aggregated function.
  • After the aggregated function you need the actual PIVOT that MS Access is using which goes after the FOR.
  • Then I did something similar to a SELECT DISTINCT budgetmonth FROM acs_Budget to get each item I could pivot by and that's where the IN came in.

Not sure if I explained very well but this is how I got mine to work!

Convert Access Crosstab/PIVOT query to T-SQL

IF OBJECT_ID('tmpEmployees_Test', 'U') IS NOT NULL DROP TABLE tmpEmployees_Test;
CREATE TABLE tmpEmployees_Test (EmployeeID INT, EmployeeName VARCHAR(255));
INSERT tmpEmployees_Test (EmployeeID, EmployeeName)
VALUES (1, 'Doe, Jane'), (2, 'Doe, John'), (3, 'Guy, Some');

IF OBJECT_ID('tmpOrders_Test', 'U') IS NOT NULL DROP TABLE tmpOrders_Test;
CREATE TABLE tmpOrders_Test (EmployeeID INT, Customer VARCHAR(255), Date DATE, OrderType VARCHAR(255));
INSERT tmpOrders_Test (EmployeeID, Customer, Date, OrderType)
VALUES (1, 'Fake Customer', '2016-05-14', 'New')
        , (2, 'Some Company', '2016-05-13', 'Change')
        , (3, 'Stuff Inc.', '2016-05-14', 'New')
        , (3, 'Cool Things', '2016-05-12', 'Change')
        , (3, 'Amazing Things', '2016-05-12', 'Change');

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;
-- Kept it for formatting Purpose
DECLARE @columns1 NVARCHAR(MAX)
SET @columns1 = N'';
SELECT @columns1 += N', ISNULL(p.' + QUOTENAME(Name) + ',''/'') AS ' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;

SET @sql = N'
SELECT EmployeeName, Count(*) as Total  ' +  @columns1  + '
FROM
(
    SELECT  EmployeeID, EmployeeName' + ''+ @columns1 + '' + '
    FROM
    (
      SELECT    o.employeeID,EmployeeName, CAST(COUNT(case WHEN OrderType = ''New'' then 1  end) as varchar(5)) + ''/'' + 
                CAST(COUNT(case WHEN OrderType = ''Change'' then 1  end) as varchar(5)) as OrderType, CONVERT(nvarchar(30) , p.Date , 101) as Date
       FROM     dbo.tmpOrders_Test AS p
                    INNER JOIN dbo.tmpEmployees_Test AS o
                     ON p.EmployeeID = o.EmployeeID
       GROUP BY EmployeeName, Date, o.employeeID
    ) AS j
    PIVOT
    (
      Max(OrderType) FOR Date IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p) as p JOIN tmpOrders_Test as m on p.employeeID = m.employeeID
where [Date] > GETDATE()-7
GROUP BY EmployeeName ' + @columns + '
';

PRINT @sql;
EXEC sp_executesql @sql;

This one is using dynamic Pivot. You might want to do this business logic on Application or Reporting Side instead of complex sql.

Convert the Pivot table in MS Access to Pivot table in SQL Server

After looking at the solution provided by @Jonathan Willcock, I was able to make up a solution for your problem. Have a look at this and perhaps you might need to refine it.

USE LinesmanRPL
DECLARE @PrerequisiteColumn AS NVARCHAR(MAX),
        @SQLforPivotTable  AS NVARCHAR(MAX);
SET @PrerequisiteColumn = STUFF((SELECT distinct ',' + QUOTENAME(Prerequisite) 
        FROM [UETTDRSB39A Criteria]
        WHERE [UETTDRSB39A Criteria].Pathway = 'Common' OR [UETTDRSB39A Criteria].Pathway = 'Electrical'
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @SQLforPivotTable = 'SELECT RPLEvidenceOptionsDetail, ' + @PrerequisiteColumn + ' FROM 
        (
          SELECT [RPL Link], RPLEvidenceOptionsDetail, Prerequisite 
          FROM [UETTDRSB39A Criteria]
        ) Temp
        PIVOT 
        (
          MIN([RPL Link]) FOR Prerequisite IN (' + @PrerequisiteColumn + ')
        ) PivotTable '
EXECUTE(@SQLforPivotTable)

Convert MS Access count and pivot query into SQL Server

Essentially, this is the classic transpose query. Consider the following SQL which should be compliant in most RDMS's. For MS Access, you would use IIF() instead of CASE WHEN (and parentheses in the joins). The crosstab query with Transform is primarily an MS Access type while Pivot() is primarily an SQL Server function. However, below should work across database dialects.

SELECT 
    dbo.Receipts.id_receipt,
    Count(CASE WHEN dbo.Product_Categories.category_name='desserts' 
               THEN dbo.Transactions.quantity END) As [desserts],
    Count(CASE WHEN dbo.Product_Categories.category_name='meat' 
               THEN dbo.Transactions.quantity END) As [meat],
    Count(CASE WHEN dbo.Product_Categories.category_name='juices' 
               THEN dbo.Transactions.quantity END) As [juices],
    Count(CASE WHEN dbo.Product_Categories.category_name='baker products' 
               THEN dbo.Transactions.quantity END) As [baker products],
    Count(CASE WHEN dbo.Product_Categories.category_name='canned' 
               THEN dbo.Transactions.quantity END) As [canned]

FROM dbo.Product_Categories
INNER JOIN dbo.Receipts 
INNER JOIN dbo.PRODUCTS 
INNER JOIN dbo.TRANSACTIONS ON dbo.PRODUCTS.id_product = dbo.TRANSACTIONS.id_product 
     ON dbo.RECEIPTS.id_receipt = dbo.TRANSACTIONS.id_receipt
     ON dbo.Product_Categories.id_prod_cut = dbo.Products.id_product 

GROUP BY dbo.Receipts.id_receipt ;

Related Topics

Imply Bit with Constant 1 or 0 in SQL Server

Tinyint VS Bit

SQL to Query Text in Access with an Apostrophe in It

Convert Integer to Text in SQLite's Select Query

SQL Query That Groups Different Items into Buckets

SQL Many to Many Select

SQL "Join" on Null Values

How to Repair a Corrupted Mptt Tree (Nested Set) in the Database Using SQL

Does the Order of Columns Matter in a Group by Clause

How to Find Records That Are Not Joined

What Free SQL Formatting Tools Exist

Update with Two Tables

Create File Encountered Operating System Error 5(Failed to Retrieve Text for This Error. Reason: 15105)

What's the Simplest Way to Import an SQLite SQL File into a Web SQL Database

Determine a Table's Primary Key Using Tsql

SQL How to Select the Most Recent Date Item

How to Generate a Series of Hourly Averages in MySQL

Referencing Outer Query's Tables in a Subquery


Leave a reply



Submit