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

I have a SQL server table load from web service. The structure of this table is not what I want.

我有一个来自web服务的SQL server表负载。这张桌子的结构不是我想要的。

My table looks like this:

我的桌子是这样的:

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

Result Table should like this:

结果表应如下:

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

I have thousands of items data in a single column, each item data contains within 3 rows. How can I transpose(rotate) it into a single row?

我在一个列中有数千个项目数据，每个项目数据包含在3行之内。如何将它转置(旋转)成一行?

I really appreciate for the help!

我真的很感激你的帮助!

1 个解决方案

#1

Here is one trick to do it

这里有一个技巧

;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

Note : This assumes RN is sequential, if no then

注意:这假设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

#1