CODEWITHSUNDEEP
sql-serverdatabaset-sql
Ronghao
Ronghao

Reputation: 13

T-SQL How to transpose a column into row by row_number SQL Server

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

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?

I really appreciate for the help!

Upvotes: 1

Views: 217

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

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 

;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

Upvotes: 2

Related Questions