'Split comma separated column data into additional columns

I have comma separated data in a column:

Column 
------- 
a,b,c,d

I want to split the comma separated data into multiple columns to get this output:

Column1  Column2 Column3 Column4 
-------  ------- ------- -------
a        b       c       d

How can this be achieved?

sqlpostgresqlsplitdelimiterpostgresql-8.4


Solution 1:[1]

split_part() does what you want in one step:

SELECT split_part(col, ',', 1) AS col1
     , split_part(col, ',', 2) AS col2
     , split_part(col, ',', 3) AS col3
     , split_part(col, ',', 4) AS col4
FROM   tbl;

Add as many lines as you have items in col (the possible maximum).
Columns exceeding data items will be empty strings ('').

Solution 2:[2]

You can use split function.

    SELECT 
    (select top 1 item from dbo.Split(FullName,',') where id=1 ) Column1,
    (select top 1 item from dbo.Split(FullName,',') where id=2 ) Column2,
    (select top 1 item from dbo.Split(FullName,',') where id=3 ) Column3,
    (select top 1 item from dbo.Split(FullName,',') where id=4 ) Column4,
    FROM MyTbl

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 vicky

Related Questions