Three methods for SQL to split a column into multiple columns

Time：2021-1-19

There is a column of data in the data table, as shown in the figure:

Now you need to divide this column into three columns.

The SQL code is as follows:

The first kind


select 
max(case when F1%3=1 then F1 else 0 end) a,
max(case when F1%3=2 then F1 else 0 end) b,
max(case when F1%3=0 then F1 else 0 end) c
from HLR151
group by (F1-1)/3

effect

The second kind


select 
c1=a.F1,c2=b.F1,c3=c.F1
from HLR151 a
left join HLR151 b on b.F1=a.F1+1 
left join HLR151 c on c.F1=a.F1+2
where (a.F1-1)%3=0

effect

The third kind


select 
max(case when (F1-1)/8=0 then F1 else 0 end) a,
max(case when (F1-1)/8=1 then F1 else 0 end) b,
max(case when (F1-1)/8=2 then F1 else 0 end) c
from HLR151
group by (F1-1)%8

effect

The above is the details of three methods of SQL splitting a column into multiple columns. For more information about SQL splitting a column into multiple columns, please pay attention to other related articles of developer!

Tags: , ,

Recommended Today

Don’t be a tool man. Touching hands teaches you Jenkins!

Hello everyone, I’m a piece of cake, a piece of cake eager to be Cai Bucai in the Internet industry. Soft or hard, praise is soft, white whoring is just!Ghost ~ remember to give me a third company after watching it! This article mainly introducesJenkins If necessary, please refer to If it helps, don’t forgetgive […]