SQL Server - 组行

Question

问题是我有一个表，其中有多个这样的行:

Name    Container       DateOrder      HourOrder 
Test1 | Container1 |    04-21-2015 |    11:00
Test1 | Container2 |    04-22-2015 |    16:00
Test2 | Container2 |    04-15-2015 |    01:00
Test3 | Container3 |    04-15-2015 |    15:00

我需要按名称、日期和容器做一份报告……我该怎么做，结果应该是这样的:

 Name     | Container  |    Container2  |   DateOrder  | DateOrder2 | HourOrder  | HourOrder2 
    Test1 | Container1 | Container2     |   04-21-2015 | 04-22-2015 |   11:00    | 16:00 
    Test2 | Container2 |                |   04-15-2015 |            |   01:00
    Test3 | Container3 |                |   04-15-2015 |            |   15:00

我还需要知道哪个日期来自哪个容器，例如 Container1 的日期是 DaterOrder1，有什么建议吗？

Answer 1

你可以使用 ROW_NUMBER() 来做这样的事情

;WITH TestTable AS 
(
SELECT 'Test1' AS Name,'Container1' AS Container,'04-21-2015' AS DateOrder,'11:00' AS HourOrder 
UNION ALL SELECT 'Test1','Container2 ','04-22-2015 ','16:00'
UNION ALL SELECT 'Test2','Container2 ','04-15-2015 ','01:00'
UNION ALL SELECT 'Test3','Container3 ','04-15-2015 ','15:00'
),
CTE AS 
(
SELECT Name,Container,DateOrder,HourOrder,ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Container ASC) rn
FROM TestTable
)

SELECT 
    C1.Name,
    C1.Container as Container,
    C2.Container as Container2,
    C1.DateOrder as DateOrder,
    C2.DateOrder as DateOrder2,
    C1.HourOrder as HourOrder,
    C2.HourOrder as HourOrder2
FROM CTE C1
LEFT JOIN CTE C2
    ON C1.Name = C2.Name
        AND C1.rn + 1 = C2.rn
WHERE C1.rn = 1