T-SQL如何通过row_number将列转置为行SQL Server

Question

我从Web服务加载了SQL Server表。这张桌子的结构不是我想要的。

我的表看起来像这样：

RN  Text 
1  ID1 
2  Title1 
3  Item1 
4  ID2 
5  Title2 
6  Item2 
7  ID3 
8  Title3 
... ... 

结果表应该是这样的：

ID  Title Item 
ID1 Title1 Item1 
ID2 Title2 Item2 
ID3 Title3 Item3 
... ...  ... 

我有成千上万个数据中的一列，每个项目数据包含3行中。我怎样才能转置（旋转）到一个单一的行？

我非常感谢您的帮助！

Answer 1

这里有一个窍门做

;WITH cte 
    AS (SELECT *, 
       seq = (Row_number()OVER(ORDER BY [RN]) - 1)/3 + 1      
     FROM Yourtable) 
SELECT ID = Max(CASE ([RN] - 1) % 3 + 1 WHEN 1 THEN [Text] END), 
     Title = Max(CASE ([RN] - 1) % 3 + 1 WHEN 2 THEN [Text] END), 
     Item = Max(CASE ([RN] - 1) % 3 + 1 WHEN 3 THEN [Text] END) 
FROM cte 
GROUP BY seq 

注：这是假定RN是连续的，如果没有那么

;WITH cte 
    AS (SELECT *, 
       seq = (Row_number()OVER(ORDER BY [RN]) - 1)/3 + 1, 
       grp = (Row_number()OVER(ORDER BY [RN]) - 1) % 3 + 1      
     FROM Yourtable) 
SELECT ID = Max(CASE grp WHEN 1 THEN [Text] END), 
     Title = Max(CASE grp WHEN 2 THEN [Text] END), 
     Item = Max(CASE grp WHEN 3 THEN [Text] END) 
FROM cte 
GROUP BY seq