Convert Access TRANSFORM/PIVOT query to SQL Server

Vipin Jain picture Vipin Jain · Dec 19, 2012 · Viewed 39k times · Source
TRANSFORM Avg(CASE WHEN [temp].[sumUnits] > 0 
                   THEN [temp].[SumAvgRent] / [temp].[sumUnits] 
                   ELSE 0 
              END) AS Expr1
SELECT [temp].[Description]
  FROM [temp] 
GROUP BY [temp].[Description]
PIVOT [temp].[Period];

Need to convert this query for sql server

I have read all other posts but unable to convert this into the same

Answer

Mahmoud Gamal picture Mahmoud Gamal · Dec 19, 2012

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 |