Casting from postgres array comma delimited values to rows and columns

Question

i have arrived at the following result set in postgres and is a bit stuck. the values came from an uploaded excel file so what i have here is a column of comma delimited values with the first row as the headers and all following as values

|   values     |
|col1,col2,col3|
|val1,val2,val3|
|val1,val2,val3|

my question is how can i arrive to my desired output described below

| col1 | col2 | col3 |
| val1 | val2 | val3 |
| cal1 | val2 | val3 |

Thanks in advance!

Does this answer your question? [Split comma separated column data into additional columns](https://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns) — VBoka, Jul 14 '20 at 05:16

score 0 · Answer 1 · answered Jul 14 '20 at 12:25

A simple method is split_part():

select split_part(col, ',', 1) as col1,
       split_part(col, ',', 2) as col2,
       split_part(col, ',', 3) as col3
from t;

However, this does not allow you to reassign the column names. That is going to be quite tricky. I would recommend that you just hard-code the values and remove the row that looks like it has the column names.

score -1 · Answer 2 · answered Jul 14 '20 at 06:23

-1

This is just a basic draft on the how part.

GetFirstRow()
.SplitByNewLine()
.SplitByComma()
.SortByIndexIntoTheeseLists(hereAreTheLists)
....
.Profit();

Hope it helps you out.

answered Jul 14 '20 at 06:23

Squirrel in training

658
1
9
23

1

I recommand you delete it, this is not an answer, because it's not answered this question. Otherwise you may will got some downvote. – Mr. Squirrel.Downy Jul 14 '20 at 06:31
1

Das my Squirrel! Although I would suggest changing the `....` to `????` would be good also. – mr5 Jul 16 '20 at 07:42

Casting from postgres array comma delimited values to rows and columns

2 Answers2