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 |
+-----------------+--------+---------+

我希望按照项目顺序合并项目，并按事务进行分区，如下所示。[项目将按顺序升序合并]

输出：

    +-----------------+--------+---------+------------------+
    |     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       |
    +-----------------+--------+---------+------------------+

- Esty

---

"CProject"下面的数字有什么意义？我没有看出明显的模式。 - jkdev

对于transNo 'A00000000000001'，CProject是按升序合并所有项目的结果。对于此交易存在4个项目100、200、201、101，将它们按ASC顺序合并后得到100101200201。 - Esty

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

- Devart

---

非常优雅的解决方案！ - shadow

太棒了！！！但有一个小问题。假设项目为100、200、200、101，是否可能返回100101200？ - Esty

嗨，Devart，[text()]是什么？它是xpath中的一个函数吗？ - Just a learner

@Ogrish Man，“text（）”函数仅检索元素值而不包括节点。 - Devart

@Smart003，不客气；）还可以查看这篇文章- http://blog.devart.com/xml-xquery-perfomance-issues.html，愿原力与你同在：） - Devart

Answer 2

尝试这种方法

SELECT t1.*,t2.CProject
FROM t AS t1
JOIN (
    SELECT SS.TransNo, (
            SELECT ' ' + US.Project     
            FROM t US
            WHERE US.TransNo = SS.TransNo
            FOR XML PATH('')
        ) CProject
    FROM t SS
    GROUP BY SS.TransNo
    ORDER BY SS.Serial
) t2 ON t1.TransNo = t2.TransNo

- Mukesh Kalgude

---

GROUP_CONCAT 在 SQL Server 上不存在。 - Devart

错误：在视图、内联函数、派生表、子查询和公共表达式中，ORDER BY 子句无效，除非还指定了 TOP、OFFSET 或 FOR XML。 - Esty

子查询中的排序可能导致意外的结果。 - Devart