SQL 将行数据连接到列中

Question

数据集:

+-----------------+--------+---------+
|     TransNo     | Serial | Project |
+-----------------+--------+---------+
| A00000000000001 |      1 |     100 |
| A00000000000001 |      2 |     200 |
| A00000000000001 |      3 |     201 |
| A00000000000001 |      4 |     101 |
| A00000000000002 |      1 |     100 |
| A00000000000002 |      2 |     101 |
| A00000000000003 |      1 |     100 |
| A00000000000003 |      2 |     200 |
| A00000000000004 |      1 |     200 |
| A00000000000004 |      2 |     100 |
| A00000000000005 |      1 |     101 |
| A00000000000005 |      2 |     100 |
+-----------------+--------+---------+

我想按项目合并新列中的项目并按交易分区，如下所示。[项目将按订单 ASC 合并]

输出:

    +-----------------+--------+---------+------------------+
    |     TransNo     | Serial | Project | CProject         |
    +-----------------+--------+---------+------------------+
    | A00000000000001 |      1 |     100 |     100101200201 |
    | A00000000000001 |      2 |     200 |     100101200201 |
    | A00000000000001 |      3 |     201 |     100101200201 |
    | A00000000000001 |      4 |     101 |     100101200201 |
    | A00000000000002 |      1 |     100 |     100101       |
    | A00000000000002 |      2 |     101 |     100101       |
    | A00000000000005 |      1 |     101 |     100101       |
    | A00000000000005 |      2 |     100 |     100101       |
    | A00000000000003 |      1 |     100 |     100200       |
    | A00000000000003 |      2 |     200 |     100200       |
    | A00000000000004 |      1 |     200 |     100200       |
    | A00000000000004 |      2 |     100 |     100200       |
    +-----------------+--------+---------+------------------+

更新 1:

如果我想按序列而不是项目来输出订单，该怎么办。

    +-----------------+--------+---------+------------------+
    |     TransNo     | Serial | Project | CProject         |
    +-----------------+--------+---------+------------------+
    | A00000000000001 |      1 |     100 |     100200201101|
    | A00000000000001 |      2 |     200 |     100200201101|
    | A00000000000001 |      3 |     201 |     100200201101|
    | A00000000000001 |      4 |     101 |     100200201101|
    | A00000000000002 |      1 |     100 |     100101       |
    | A00000000000002 |      2 |     101 |     100101       |
    | A00000000000005 |      1 |     101 |     101100       |
    | A00000000000005 |      2 |     100 |     101100       |
    | A00000000000003 |      1 |     100 |     100200       |
    | A00000000000003 |      2 |     200 |     100200       |
    | A00000000000004 |      1 |     200 |     200100       |
    | A00000000000004 |      2 |     100 |     200100       |
    +-----------------+--------+---------+------------------+

Answer 1

DECLARE @t TABLE (
    TransNo VARCHAR(20),
    Serial INT,
    Project INT
)

INSERT INTO @t (TransNo, Serial, Project)
VALUES
    ('A00000000000001', 1, 100),
    ('A00000000000001', 2, 200),
    ('A00000000000001', 3, 201),
    ('A00000000000001', 4, 101),
    ('A00000000000002', 1, 100),
    ('A00000000000002', 2, 101),
    ('A00000000000003', 1, 100),
    ('A00000000000003', 2, 200),
    ('A00000000000004', 1, 200),
    ('A00000000000004', 2, 100),
    ('A00000000000005', 1, 101),
    ('A00000000000005', 2, 100)

SELECT *, CProject = (
    SELECT DISTINCT [text()] = t2.Project
    FROM @t t2
    WHERE t2.TransNo = t1.TransNo
    ORDER BY t2.Project
    FOR XML PATH('')
)
FROM @t t1

输出-

TransNo              Serial      Project     CProject
-------------------- ----------- ----------- --------------
A00000000000001      1           100         100101200201
A00000000000001      2           200         100101200201
A00000000000001      3           201         100101200201
A00000000000001      4           101         100101200201
A00000000000002      1           100         100101
A00000000000002      2           101         100101
A00000000000003      1           100         100200
A00000000000003      2           200         100200
A00000000000004      1           200         100200
A00000000000004      2           100         100200
A00000000000005      1           101         100101
A00000000000005      2           100         100101

使用[text()] -

100101200201

没有[text()] -

<Project>100</Project><Project>101</Project><Project>200</Project><Project>201</Project>

更多详情- http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

更新 -

SELECT *, CProject = (
    SELECT [text()] = t2.Project
    FROM (
        SELECT t2.Project, Serial = MIN(t2.Serial)
        FROM @t t2
        WHERE t2.TransNo = t1.TransNo
        GROUP BY t2.Project
    ) t2
    ORDER BY t2.Serial
    FOR XML PATH('')
)
FROM @t t1