代码之家 › 专栏 › 技术社区 › Leace

在SQL中将水平表转换为垂直表

pivot-table pivot sql-server sql

Leace · 技术社区 · 6 年前

我有以下表格格式:

我需要将此表转换为以下格式:

我在其他问题中寻找了pivot函数,但是输入表中的“key”值不是一组固定的值,它们可以是任何值。我也找过其他类似的问题,但在我的案例中,我不确定该如何编写查询。

我的代码是:

选择
行号()结束(
按routecode排序)作为srno
,路由代码为x
,和(单位)为y
从
[接口\u OK]。[DBO]。[V_A40ordersbhqt]
其中[DeliveryDate]强制转换(
floor(cast(getdate()as float))as datetime
)
客户代码如“900%”
分组依据[路由代码]
感谢您的帮助!

我需要将此表转换为以下格式:



我在其他问题中寻找了pivot函数,但是输入表中的“key”值不是一组固定的值,它们可以是任何值。我还寻找了其他类似的问题,但在我的案例中,我不确定该如何编写查询。

我的代码是:



SELECT
    ROW_NUMBER () OVER (
ORDER BY RouteCode) AS SrNo
, RouteCode AS X
, SUM(Units) AS Y
FROM
    [ INTERFACE_ok ] .[ dbo ] .[ v_A40OrdersBhQt ]
WHERE [ DeliveryDate ] > CAST(
        FLOOR(CAST(GETDATE () AS FLOAT)) AS DATETIME
    )
    AND CustomerCode LIKE '900%'
GROUP BY [ RouteCode ]


感谢您的帮助!

2 回复 | 直到 6 年前

D-Shih 6 年前

你在找 动力学枢轴 .

主要步骤如下

声明变量 @sqlX 和 @sqlY 带着你的 MAX 功能和 CASW WHEN 要创建的表达式 X 和 Y 旋转列数。
使用 CONCAT 当表达式字符串和主选择字符串以及 UNION ALL @平方英尺 和 @平方 查询字符串。
使用 EXECUTE 函数动态执行SQL。

测试dll

CREATE TABLE T(
  SrNo INT,
  X  VARCHAR(100),
  Y INT
);

INSERT INTO T VALUES (1,'N1',100);
INSERT INTO T VALUES (2,'N2',200);
INSERT INTO T VALUES (3,'N3',300);
INSERT INTO T VALUES (4,'N4',400);
INSERT INTO T VALUES (5,'N5',500);
INSERT INTO T VALUES (6,'N6',600);
INSERT INTO T VALUES (7,'N7',700);

这是MySQL示例。

SET @sqlX = NULL;
SET @sqlY = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN SrNo =',
      SrNo,
      ' THEN X END) '
    )
  ) INTO @sqlX
FROM T;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN SrNo =',
      SrNo,
      ' THEN Y END) '
    )
  ) INTO @sqlY
FROM T;

SET @sql = CONCAT('SELECT ''X'', ', @sqlX, ' 
                   FROM T
                   UNION ALL
                   SELECT ''Y'', ', @sqlY, '
                   FROM T
                   ');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Mysql sqlfiddle

SQL Server版本

DECLARE @sqlX VARCHAR(MAX)
DECLARE @sqlY VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @sqlX = STUFF((SELECT distinct ', CAST( MAX(CASE WHEN SrNo =' + CAST(SrNo AS VARCHAR(5)) +  ' THEN X END) AS VARCHAR(MAX)) '
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @sqlY = STUFF((SELECT distinct ',CAST( MAX(CASE WHEN SrNo = ' + CAST(SrNo AS VARCHAR(5)) +  ' THEN Y END) AS VARCHAR(MAX)) '
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @sql = CONCAT('SELECT ''X'', ', @sqlX, ' 
                   FROM T
                   UNION ALL
                   SELECT ''Y'', ', @sqlY, '
                   FROM T');

execute(@sql)

sqlserver sqlfiddle

结果

    | X | MAX(CASE WHEN SrNo =1 THEN X END) | MAX(CASE WHEN SrNo =2 THEN X END) | MAX(CASE WHEN SrNo =3 THEN X END) | MAX(CASE WHEN SrNo =4 THEN X END) | MAX(CASE WHEN SrNo =5 THEN X END) | MAX(CASE WHEN SrNo =6 THEN X END) | MAX(CASE WHEN SrNo =7 THEN X END) |
    |---|-----------------------------------|-----------------------------------|-----------------------------------|-----------------------------------|-----------------------------------|-----------------------------------|-----------------------------------|
    | X |                                N1 |                                N2 |                                N3 |                                N4 |                                N5 |                                N6 |                                N7 |
    | Y |                               100 |                               200 |                               300 |                               400 |                               500 |                               600 |                               700 |

注释 : T 不能代替子查询或当前结果集。

Paurian 6 年前

Aaron Bertrand写了一篇适合你需要的文章: https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

USE tempdb;
GO
CREATE TABLE dbo.Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
(
  OrderID INT,
  ProductID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Products(ProductID),
  Quantity INT
  /* other columns */
);
INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);

他的动态解决方案是在pivot语句中应用一种东西:

编写收集列的子查询:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name)
  FROM (SELECT p.Name FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name) AS x;

然后创建可执行SQL:

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;

最后,运行它:

EXEC sp_executesql @sql;