CODEWITHSUNDEEP
sqlsql-serverpivot
Fahmin Rahman
Fahmin Rahman

Reputation: 55

Splitting data from one column into multiple rows in SQL Server

I am a beginner in SQL and unfortunately I am not being able to solve a problem. I have a list of column names in a system table which I want to insert into separate rows of a table based on the serial of the column name.

Original table:

Column name
001000
001100
001200
002000
002100
002200
002300

Target table:

Column 1 Column 2 Column 3 Column 4
001000 001100 001200 NULL
002000 002100 002200 002300
...... ...... ...... ......
022000 022100 022200 022300

There are a large number of rows in the original table so it is quite challenging to separate the numbers into groups. I have looked into the PIVOT function but that does not solve this issue.

Unfortunately, as I am a beginner, I am also not able to put it into code to begin with. Any help with recommending which functions I should look into so that I can start writing some code on this would be really helpful. Any pointers towards a solution is also highly appreciated.

Upvotes: 4

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Conditional aggregation should do what you want:

select max(case when seqnum = 1 then column end),
       max(case when seqnum = 2 then column end),
       max(case when seqnum = 3 then column end),
       max(case when seqnum = 4 then column end)
from (select t.*,
             row_number() over (partition by left(column, 3) order by column) as seqnum
      from t
     ) t
group by left(column, 3)

Upvotes: 6

Related Questions