sql-server - 使用多列有效地将行旋转/转置到列中

Question

假设我有这样的数据:

  Table
  Num1       Type1       Code       Group      DA        Account      Value
  1X2        GG          XX1        INTS       1         123          75.00
  1X2        GG          XX1        INTS       1         234          100.00

我想做的是旋转数据，使其看起来像这样:

  Num1       Type1       Code       Group      DA      123        234
  1X2        GG          XX1        INTS       1       75.00      100.00

我不太确定如何使用 PIVOT 完成这样的事情，但我确实尝试了以下方法:

 Select Num1, 
        Type1, 
        Code, 
        Group, 
        DA, 
        '123' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
        '234' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
 From Table t1
  

但是我得到的错误是:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

在那种情况下，我将“Top 1”添加到每个子查询:

Select Num1, 
        Type1, 
        Code, 
        Group, 
        DA, 
        '123' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
        '234' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
 From Table t1

但是，即使查询现在为每个帐户返回两行 AMOUNTS，我并不完全理解 TOP 1 的目的，但基本上现在的数据如下所示:

 Num1       Type1       Code       Group      DA        123        234
  1X2        GG          XX1        INTS       1        NULL       100.00 
  1X2        GG          XX1        INTS       1        75.00      NULL

我想这还不错，因为我可以做 MAX(123)和 MAX(234)与 GROUP BY在所有其他列上，最后是 1 行。

有更好的方法吗？这可以通过 PIVOT 实现吗？

Answer 1

为此你需要像 MAX 和 GROUP BY 这样的聚合函数

CREATE TABLE  Table1
    ([Num1] varchar(3), [Type1] varchar(2), [Code] varchar(3), [Group] varchar(4), [DA] int, [Account] int, [Value] DECIMAL(10,2))
;
    
INSERT INTO  Table1
    ([Num1], [Type1], [Code], [Group], [DA], [Account], [Value])
VALUES
    ('1X2', 'GG', 'XX1', 'INTS', 1, 123, 75.00),
    ('1X2', 'GG', 'XX1', 'INTS', 1, 234, 100.00)
;

GO

SELECT
[Num1], [Type1], [Code], [Group],
MAX(CASE WHEN [Account] = 123 THEN [Value] ELSe -999999999999 END) AS [123],
MAX(CASE WHEN [Account] = 234 THEN [Value] ELSe -9999999999 END) AS [234]
FROM Table1
 GROUP BY  [Num1], [Type1], [Code], [Group]
GO

Num1 | Type1 | Code | Group |   123 |    234:--- | :---- | :--- | :---- | ----: | -----:1X2  | GG    | XX1  | INTS  | 75.00 | 100.00

db<> fiddle here