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?
This question is related to
postgresql
split
delimiter
postgresql-8.4
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 (''
).
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
Source: Stackoverflow.com