时间戳

首页 > 解决方案 > 编写一个 sql 查询以使用数据透视在单行中捕获以下信息

sql - 编写一个 sql 查询以使用数据透视在单行中捕获以下信息

问题描述

我有一个包含三个字段的表，例如名称、类型和金额

CREATE TABLE Customers
(
    [Name] Varchar, 
    [Type] Varchar, 
    [Amount] int
);

INSERT INTO Customers (Name, Type, Amount)
VALUES ('Corporate', 'General',  300),
       ('Corporate', 'General',  400),
       ('Corporate', 'Specific',  4008);

我希望我的输出和下面一样

+-----------+---------+--------+---------+--------+----------+--------+
|   Name    |  Type   | Amount |  Type   | Amount |   Type   | Amount |
+-----------+---------+--------+---------+--------+----------+--------+
| Corporate | General |    300 | General |    400 | Specific |   4008 |
+-----------+---------+--------+---------+--------+----------+--------+

标签： sqlpivot

解决方案

我们也许可以在ROW_NUMBER这里使用一个技巧来旋转：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Amount) rn
    FROM Customers
)

SELECT
    Name,
    MAX(CASE WHEN rn = 1 THEN Type END) AS Type1,
    MAX(CASE WHEN rn = 1 THEN Amount END) AS Amount1,
    MAX(CASE WHEN rn = 2 THEN Type END) AS Type2,
    MAX(CASE WHEN rn = 2 THEN Amount END) AS Amount2,
    MAX(CASE WHEN rn = 3 THEN Type END) AS Type3,
    MAX(CASE WHEN rn = 3 THEN Amount END) AS Amount3
FROM cte
GROUP BY
    Name;

推荐阅读