Sql将行数据连接到列中 [英] Sql concatenate of row data into column
本文介绍了Sql将行数据连接到列中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
数据集:
+-----------------+--------+---------+
| 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合并]
I want to merge projects in a new column order by project & partition by transaction as following. [Project will be merged by order 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:
如果我想要按串行而不是项目的输出顺序该怎么办.
What to do if I want my output order by Serial instead of project.
+-----------------+--------+---------+------------------+
| 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 |
+-----------------+--------+---------+------------------+
推荐答案
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
这篇关于Sql将行数据连接到列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
