How to merge rows to one row in SQL Server

I have a table like this:

 - ID  | CurrencyID | LendID |  Price
 - 3   |    1       |  1      |  1.2
 - 3   |    1       |  2      |  1.3
 - 3   |    1       |  3 开发者_Python百科     |  1.4
 - 3   |    2       |  1      |  1.5
 - 3   |    2       |  2      |  1.6
 - 3   |    2       |  3      |  1.7
 - 4   |    2       |  3      |  2.0

There are totally 4 currencies 1,2,3,4

There are totally 3 lend 1,2,3

I want to get a result like below:

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

I know it is now good description, but what I want to do is to merge many records to one record.

This is called pivoting and one of the ways how to do the pivoting is to use grouping with conditional aggregating:

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

If all the (CurrencyID, LendID) combinations are guaranteed to be unique within same ID groups, you can also use MIN or MAX instead of SUM.

You could use the PIVOT Syntax starting with SQL Server 2005

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

which outputs

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

Note: I kept your column names