如何在 SQL Server 中将行合并为一行

Question

我有一个这样的表：

 - ID  | CurrencyID | LendID |  Price
 - 3   |    1       |  1      |  1.2
 - 3   |    1       |  2      |  1.3
 - 3   |    1       |  3      |  1.4
 - 3   |    2       |  1      |  1.5
 - 3   |    2       |  2      |  1.6
 - 3   |    2       |  3      |  1.7
 - 4   |    2       |  3      |  2.0

总共有 4 种货币 1,2,3,4

总共有 3 种借出 1,2,3

我想得到这样的结果如下：

ID | CurrencyIDLendID_11_Price | CIDID_12_Price | CIDLID_13_Price | CIDLID_21_Price | CIDLID_22_Price | CIDLID_23_Price | CIDLID_31_Price | CIDLID_32_Price | CIDLID_33_Price | CIDLID_41_Price | CIDLID_42_Price | CIDLID_43_Price
 3 | 1.2 | 1.3 | 1.4 | 1.5 | 1.6 | 1.7 | 0 | 0 | 0 | 0 | 0 | 0
 4 |  0  |  0  |  0  |  0  |  0  | 2.0 | 0 | 0 | 0 | 0 | 0 | 0

我知道现在这是很好的描述，但我想做的是将多条记录合并为一条记录。

Answer 1

这称为透视，进行透视的方法之一是使用带条件聚合的分组：

WITH cidlid AS (
  SELECT
    ID,
    CurrencyIDLendID = CurrencyID * 10 + LendID,
    Price
  FROM atable
)
SELECT
  ID,
  CurrencyIDLendID_11_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 11 THEN Price END), 0),
  CurrencyIDLendID_12_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 12 THEN Price END), 0),
  CurrencyIDLendID_13_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 13 THEN Price END), 0),
  CurrencyIDLendID_21_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 21 THEN Price END), 0),
  CurrencyIDLendID_22_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 22 THEN Price END), 0),
  CurrencyIDLendID_23_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 23 THEN Price END), 0),
  CurrencyIDLendID_31_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 31 THEN Price END), 0),
  CurrencyIDLendID_32_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 32 THEN Price END), 0),
  CurrencyIDLendID_33_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 33 THEN Price END), 0),
  CurrencyIDLendID_41_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 41 THEN Price END), 0),
  CurrencyIDLendID_42_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 42 THEN Price END), 0),
  CurrencyIDLendID_43_Price = COALESCE(SUM(CASE CurrencyIDLendID WHEN 43 THEN Price END), 0)
FROM cidlid
GROUP BY ID

如果保证所有 (CurrencyID, LendID) 组合都是唯一的在相同的 ID 组中，您还可以使用 MIN 或 MAX 来代替 SUM。

Answer 2

您可以使用从 SQL Server 2005 开始的 PIVOT 语法，

Declare @Data table (ID int, CurrencyID int, LendID int , price decimal (4,2))

INSERT INTO @Data

SELECT 3 as ID, 1 as CurrencyID, 1 as LendID , 1.2  as price
UNION SELECT 3   ,    1       ,  1      ,  1.2
UNION SELECT 3   ,    1       ,  2      ,  1.3
UNION SELECT 3   ,    1       ,  3      ,  1.4
UNION SELECT 3   ,    2       ,  1      ,  1.5
UNION SELECT 3   ,    2       ,  2      ,  1.6
UNION SELECT 3   ,    2       ,  3      ,  1.7
UNION SELECT 4   ,    2       ,  3      ,  2.0



SELECT 
    ID,
    COALESCE([1_1],0)  as CurrencyIDLendID_11_Price ,
    COALESCE([1_2],0)  as CIDID_12_Price  ,
    COALESCE([1_3],0)  as CIDLID_13_Price  ,
    COALESCE([2_1],0)  as CIDLID_21_Price  ,
    COALESCE([2_2],0)  as CIDLID_22_Price  ,
    COALESCE([2_3],0)  as CIDLID_23_Price  ,
    COALESCE([3_1],0)  as CIDLID_31_Price  ,
    COALESCE([3_2],0)  as CIDLID_32_Price  ,
    COALESCE([3_3],0)  as CIDLID_33_Price  ,
    COALESCE([4_1],0)  as CIDLID_41_Price  ,
    COALESCE([4_2],0)  as CIDLID_42_Price  ,
    COALESCE([4_3],0)  as CIDLID_43_Price  
 FROM (
 SELECT 
        ID,
        cast(CurrencyID as varchar) + '_' + CAST(lendID as varchar) ColumnHeader, 
        price FROM @Data ) src 
 PIVOT (SUM(price) for ColumnHeader IN 
        ([1_1], [1_2],[1_3], 
             [2_1], [2_2],[2_3],
             [3_1], [3_2],[3_3],
             [4_1], [4_2],[4_3])


 ) as pivottable

其输出

ID          CurrencyIDLendID_11_Price               CIDID_12_Price                          CIDLID_13_Price                         CIDLID_21_Price                         CIDLID_22_Price                         CIDLID_23_Price                         CIDLID_31_Price                         CIDLID_32_Price                         CIDLID_33_Price                         CIDLID_41_Price                         CIDLID_42_Price                         CIDLID_43_Price
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
3           1.20                                    1.30                                    1.40                                    1.50                                    1.60                                    1.70                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00
4           0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    2.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00

注意：我保留了你的专栏名称