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行或更多行。所以我想分成3组列。

Answer 1

您应该能够使用PIVOT来实现此结果。请参阅PIVOT Example from documentation。像下面这样的东西...

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;