sql - SQL Server-组行

问题是我有一个表，其中有多行如下：

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，有什么建议吗？

最佳答案

你可以用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

关于sql - SQL Server-组行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29787372/