将Access TRANSFORM/PIVOT查询转换为SQL Server [英] Convert Access TRANSFORM/PIVOT query to 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服务器

Need to convert this query for sql server

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

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

推荐答案

此处是使用

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小提琴演示

例如，这将为您提供:

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 |

这篇关于将Access TRANSFORM/PIVOT查询转换为SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！