将行高效地透视/转置为具有多列的列

2022-06-29 00:00:00 pivot sql-server ssms

假设我有如下数据:

  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

但是,尽管查询现在返回两行每个帐户的金额,我并不完全理解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),最后得到1行。

有没有更好的方法来解决这个问题?这可以通过Pivot实现吗?


解决方案

SELECT
  [Num1], 
  [Type1], 
  [Code], 
  [Group], 
  [DA], 
  [123],
  [234]
FROM
  yourTable
PIVOT
(
  MAX([value])  
  FOR [account] IN ([123], [234])
)
  AS PivotTable

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f

相关文章