sql-server - 使用 Pivot 或 Unpivot 将值分配给特定列

Question

我有一个具有以下结构的表：

+-------+-----------+--------+----------+--------+
| RefNo |  TranType |  Code  | Remarks  | Amount |
+-------+-----------+--------+----------+--------+
|     1 | BD        | 400201 | abcc dfr |    200 |
|     1 | BD        | 400202 | abcc dfr |    200 |
|     2 | BD        | 400204 | defrt    |    300 |
|     2 | BD        | 400205 | defrt    |    300 |
+-------+-----------+--------+----------+--------+

我需要将这些值转换为以下格式：

+-------+--------+--------+----------+----------+--------+
| RefNo | Code1  | Code2  | TranType | Remarks  | Amount |
+-------+--------+--------+----------+----------+--------+
|     1 | 400201 | 400202 | BD       | abcc dfr |    200 |
|     2 | 400204 | 400205 | BD       | defrt    |    300 |
+-------+--------+--------+----------+----------+--------+

标签： sql-server

Answer 1

您不需要使用PIVOT，您可以使用简单的查询来完成。

SELECT t1.refno, 
       t1.code AS Code1, 
       t2.code AS Code2, 
       t1.trantype, 
       t1.amount 
FROM   @table t1 
       INNER JOIN @table t2 
               ON t1.refno = t2.refno                      
                  AND T1.code < T2.code 

在线演示