SQL Server 转换 3 行，其中 3 列在 9 列一行中

Question

我有这个查询：

SELECT TOP 3 
A.Id,
A.NAME as Name,
B.Rate as PayRate,
C.Description as Currency
FROM 
TABLEA A 
JOIN 
TABLEB B ON A.id = B.TableAId
JOIN 
TABLEC C ON B.Id = C.TableBId
WHERE 
TABLEA.FieldX = 1
AND TABLEB.FieldX = 3

这将返回如下结果：

Id      Name    PayRate     Currency
-------------------------------------
2503    John    110.00      Dollar
2503    Mike      5.00      EURO
2503    Erik     10.00      Dollar
2504    Rob       2.00      EURO
2504    Elis     11.00      Dollar
2505    May       4.00      Dollar

但我想返回这样的东西：

Id      Name01  PayRate01 Currency01  Name02  PayRate02 Currency02  Name03  PayRate03   Currency03
--------------------------------------------------------------------------------------------------
2503    John    110.00    Dollar      Mike     5.00     EURO        Erik    10.00       Dollar
2504    Rob       2.00    EURO        Elis    11.00     Dollar      Null    Null        Null
2505    May       4.00    Dollar      Null    Null      Null        Null    Null        Null

这不是固定查询，这只是一个例子，它将从同一ID带来许多记录。有些可能有 1 行、2 行、3 行或更多行。所以无论何时ID，我都想分成3组列。

Answer 1

您应该能够使用PIVOT来实现此结果。请参阅文档中的 PIVOT 示例。下面这样...

SELECT id, [0] AS Name01, [1] AS Name02, [2] AS Name03
FROM   
(SELECT id, name
FROM table) p  
PIVOT  
(  
name 
FOR id IN  
( [0], [1], [2] )  
) AS pvt  
ORDER BY pvt.id;  

如果数据是固定的，那么一种方法是使用如下所示case语句。

Select 
id, 
max(case when name='John' then payrate else null end) PayRate01,
max(case when name='John' then currency else null end) Currency01,
max(case when name='John' then name else null end) Name01,
max(case when name='Mike' then payrate else null end) PayRate02,
...
...
from table
group by id

Answer 2

您可以将条件聚合与row_number()一起使用：

select id,
max(case when seqnum = 1 then name end) as name_1,
max(case when seqnum = 1 then payrate end) as payrate_1,
max(case when seqnum = 1 then currency end) as currency_1,
max(case when seqnum = 2 then name end) as name_2,
max(case when seqnum = 2 then payrate end) as payrate_2,
max(case when seqnum = 2 then currency end) as currency_2,
max(case when seqnum = 3 then name end) as name_3,
max(case when seqnum = 3 then payrate end) as payrate_3,
max(case when seqnum = 3 then currency end) as currency_3
from (select t.*,
row_number() over (partition by id order by id) as seqnum
from t
) t
group by id;