根据序列号连接行

Question

>我有一个表格，如下所示：

DECLARE @T AS TABLE
(
SeqNumber INT, 
Note VARCHAR(MAX),
UserID INT
)
INSERT INTO @T (SeqNumber, Note, UserID) VALUES
(1, 'A', 20),
(2, 'B', 20),
(1, 'F', 20),
(2, 'K', 20),
(3, 'M', 20),
(1, 'X', 20),
(1, 'Y', 20)

我希望创建如下结果：

UserID   ResultNotes
20       'AB'
20       'FKM'
20       'X'
20       'Y'

您知道如何在MS SQL Server(T-SQL(中实现这一点吗？它可以在MS SQL Server 2016或更早版本中。

谢谢

Answer 1

这是一开始的评论，但我决定从中准备一个答案......

您是否知道没有隐式排序顺序？ 似乎您想在SeqNumber为1时启动一个新组。所以 - 在你的样本中 - A 和 B 是第一组，FKM 是第二组，依此类推。但是如果没有额外的排序顺序或任何类型的分组键，则不会对此集合进行排序！这可能会以任何随机顺序返回...

这是使用STRING_AGG进行分组键的方法(需要v2017+(：

DECLARE @T AS TABLE
(
GroupKey INT,
SeqNumber INT, 
Note VARCHAR(MAX),
UserID INT
)
INSERT INTO @T (GroupKey,SeqNumber, Note, UserID) VALUES
(1, 1, 'A', 20),
(1, 2, 'B', 20),
(2, 1, 'F', 20),
(2, 2, 'K', 20),
(2, 3, 'M', 20),
(3, 1, 'X', 20),
(4, 1, 'Y', 20);
SELECT t.UserID
,STRING_AGG(Note,'') WITHIN GROUP(ORDER BY t.SeqNumber) AS ResultNotes
FROM @T t
GROUP BY t.GroupKey,t.UserID;

这适用于 2017 年之前的版本：

SELECT t.UserID
,(
SELECT t2.Note AS [*]
FROM @T t2
WHERE t.GroupKey=t2.GroupKey
AND t.UserID=t2.UserID
ORDER BY t2.SeqNumber
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)') AS ResultNotes
FROM @T t
GROUP BY t.GroupKey,t.UserID

更新：从固定顺序创建分组键

如果排序顺序是固定的(在我的例子中是IDENTITY-ID(，请参阅如何创建分组键的代码：

DECLARE @T AS TABLE
(
ID INT IDENTITY,
SeqNumber INT, 
Note VARCHAR(MAX),
UserID INT
)
INSERT INTO @T (SeqNumber, Note, UserID) VALUES
(1, 'A', 20),
(2, 'B', 20),
(1, 'F', 20),
(2, 'K', 20),
(3, 'M', 20),
(1, 'X', 20),
(1, 'Y', 20);
WITH Get1s AS --set a group key per "1"
(
SELECT t.ID,ROW_NUMBER() OVER(ORDER BY ID) AS GroupKey
FROM @T t
WHERE t.SeqNumber=1
)
SELECT *
,(SELECT MAX(x.GroupKey) FROM Get1s x WHERE t.ID>=x.ID) AS GroupKey
FROM @T t;

在下一步中，您可以在上述方法中使用此分组键...

Answer 2

DECLARE @T AS TABLE
(
SeqNumber INT, 
Note VARCHAR(MAX),
UserID INT
)
INSERT INTO @T (SeqNumber, Note, UserID) VALUES
(1, 'A', 20),
(2, 'B', 20),
(1, 'F', 20),
(2, 'K', 20),
(3, 'M', 20),
(1, 'X', 20),
(1, 'Y', 20)
drop table if exists #d
select  ROW_NUMBER() OVER(ORDER BY userid ASC) AS Row#, case when SeqNumber = 1 then 1 else 0 end as t , SeqNumber,note,userid into #d from @t 
select userid,string_agg(Note,'') from
(
select isnull(t + (select sum(t) from #d as g where g.row# < #d.row#),1) as s , SeqNumber,note,userid from #d
) j
group by s,userid

Answer 3

你可以试试这个：

如果 seqNum 从 1 开始并递增 1，则此解决方案将起作用

DECLARE @T AS TABLE
(
SeqNumber INT, 
Note VARCHAR(MAX),
UserID INT
)
INSERT INTO @T (SeqNumber, Note, UserID) VALUES
(1, 'A', 20),
(2, 'B', 20),
(3, 'C', 20),
(4, 'D', 20),
(5, 'E', 20),
(1, 'F', 20),
(2, 'K', 20),
(3, 'M', 20),
(1, 'X', 20),
(1, 'Y', 20)

;with cte1 as
(
select *,
CASE WHEN LEAD(SeqNumber) OVER(partition by userId order by (select null))>SeqNumber THEN  LEAD(SeqNumber) OVER(order by (select null))
ELSE NULL END as leadingSeq,
ROW_NUMBER()over(partition by userId order by (select null)) as rowNum
from @T
)
,
cte2
As
(
select c1.SeqNumber,c1.UserId,ISNULL(c1.Note,'')+ISNULL(c2.Note,'') as Note 
,c2.leadingSeq,c2.rowNum    
from cte1 c1
LEFT JOIN cte1 c2 on c1.leadingSeq=c2.SeqNumber and c2.rowNum=c1.rowNum+1 and c1.SeqNumber=1 
Where c1.SeqNumber=1 
or c2.Note IS NOT NULL
Union All
Select c1.SeqNumber,c1.UserId,ISNULL(c1.Note,'')+ISNULL(c2.Note,'') as SecNote 
,c2.leadingSeq,c2.rowNum     
from cte2 c1
JOIN cte1 c2 on c1.leadingSeq=c2.SeqNumber and c2.rowNum=c1.rowNum+1

)
select UserId,Note 
from cte2 
where leadingSeq is null