将 Access TRANSFORM/PIVOT 查询转换为 SQL Server

2021-12-12 00:00:00 pivot ms-access sql tsql sql-server 
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];

需要将此查询转换为 sql server

Need to convert this query for sql server

我已阅读所有其他帖子，但无法将其转换为相同内容

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

推荐答案

这是使用 PIVOT 表操作符:

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 演示

例如，这会给你:

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 |

<小时>

注意 使用 MS SQL Server PIVOT 表运算符时，您必须输入透视列的值.但是，在 MS Access 中，这是 TRANSFORM 和 PIVOT 所做的工作，即动态获取透视列的值.在这种情况下，您必须使用 PIVOT 运算符动态执行此操作，如下所示:

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);

更新的 SQL Fiddle 演示

这应该给你相同的结果:

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 |

相关文章