如何将数据从另一表的一列拆分为三列?的SQL
问题描述：
我有带有两列id_student和hobbies的表(inf)，如下所示:
I have table (inf) with two columns id_student and hobbies like this:
ID_student = 1
Hobbies = "music, cooking, bassguitar"
我想复制兴趣爱好，方法是将其拆分为包含以下各列的三列到另一个表(兴趣爱好):
and I want to copy the hobbies by splitting them to another table (hobbies) in three columns which contain the following columns:
ID_student hobby1 hobby2 hobby3
1 music cooking bassguitar
我该如何在Postgres中写类似的东西?
how could I write something likethat in Postgres?
答
有很多方法可以做到这一点. 一种方法是使用 string_to_array 函数:
There is many ways to do this. One way is using the string_to_array function:
INSERT INTO hobbies (id, hobby1, hobby2, hobby3)
SELECT id,hobbies_array[1],hobbies_array[2],hobbies_array[3] FROM
(
SELECT id,string_to_array(hobbies,',') AS hobbies_array
FROM inf
) AS foo;