sql - 在Sql中将水平表转换为垂直表

问题描述

我有以下表格格式：

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

我在其他问题中寻找 PIVOT 函数，但输入表中的“键”值不是一组固定的值，它们可以是任何值。我还寻找了其他此类问题，但我不确定应该如何编写查询。

我的代码是：

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 ]

任何帮助将不胜感激，谢谢！

标签： sqlsql-serverpivotpivot-table

您正在寻找Dynamics pivot。

主要步骤如下

声明变量@sqlX并@sqlY携带您的MAX函数和CASW WHEN表达式来创建X和Y透视列。
用于CONCAT组合您的 SUM 函数和 CASW WHEN 表达式字符串和主选择字符串UNION ALL @sqlX以及@sqlY查询字符串。
使用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 服务器版本

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可以代替您的子查询或当前结果集。

sql - 在Sql中将水平表转换为垂直表

问题描述

解决方案

推荐阅读