如何在SQL Server中将行合并为一行
如何在SQL Server中将行合并为一行
提问于 2011-07-07 11:02:56
我有一张这样的桌子：
- 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个lend 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我知道它现在是很好的描述，但我想要做的是将多个记录合并到一个记录中。
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-07-07 12:33:32
这被称为旋转，旋转的方法之一是使用分组和条件聚合：
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。
Stack Overflow用户
发布于 2011-07-08 01:39:07
可以从SQL Server2005开始使用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注意:我保留了您的列名
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6605363
复制相关文章
相似问题
腾讯云开发者
