Re: CASE(?) to write in a different column based on a string pattern

So what you are doing is transforming the table format from vertical to
horizontal. I think you will want to use a union to join the table to
itself along with the case statement to produce the output you are looking
for.

On Wed, Nov 13, 2019 at 10:37 AM Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> On Wed, 13 Nov 2019 at 16:24, Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
> wrote:
> > |foo |bar |baz |
> > 1234
> > 5678
> > 9012
> > (hoping text formatting is ok... 1234 should go in column foo, 568 in
> > bar and 9012 in baz)
> >
> > Is it possible?
>
> Simplest way in plain SQL would be individual case statements for each
> column, I think.
>
> SELECT pattern,
> CASE WHEN pattern LIKE 'foo%' THEN SUBSTR(pattern, 4) ELSE '' END AS foo
> CASE WHEN pattern LIKE 'bar%' THEN SUBSTR(pattern, 4) ELSE '' END AS bar
> CASE WHEN pattern LIKE 'baz%' THEN SUBSTR(pattern, 4) ELSE '' END AS baz
> FROM tbl;
>
> Geoff
>
>
>

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'