MIME-Version: 1.0
References: <CAAcmDX_KU4A1DWzKmO4XacVQ72+0KZ9rZSvQwHkCWgpqB-Lb2w@mail.gmail.com>
 <aQjkwyDjsz8Cgp0X@depesz.com>
In-Reply-To: <aQjkwyDjsz8Cgp0X@depesz.com>
From: dfgpostgres <dfgpostgres3@gmail.com>
Date: Mon, 3 Nov 2025 12:32:09 -0500
Message-ID: <CAAcmDX8guTsQUb8FjRiX2i4PY+U-iqNGaY649Z6q+skcKMPn5g@mail.gmail.com>
Subject: Re: PG Unpivot ?
To: depesz@depesz.com
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f27ede0642b4186b"
Archived-At: <https://www.postgresql.org/message-id/CAAcmDX8guTsQUb8FjRiX2i4PY%2BU-iqNGaY649Z6q%2BskcKMPn5g%40mail.gmail.com>
Precedence: bulk

--000000000000f27ede0642b4186b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Wow !
That did it, even with the predicate I stuck on the end when I tried it.

Thanks Depesz !

On Mon, Nov 3, 2025 at 12:22=E2=80=AFPM hubert depesz lubaczewski <depesz@d=
epesz.com>
wrote:

> On Mon, Nov 03, 2025 at 12:18:55PM -0500, dfgpostgres wrote:
> > psql (13.2, server 15.3) on linux
> >
> > I think they call this "unpivot" in MSSQL ?
> >
> > How can I get an sql query to return  one line per column with... an ID=
,
> > column name and value.  the ctid for the id field is fine.
> >
> > Example:
> > dvdb=3D# create table unpivot (intcol integer, floatcol float, strcol
> > varchar);
> > CREATE TABLE
> > dvdb=3D# insert into unpivot (intcol,floatcol,strcol) values
> > (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three');
> > INSERT 0 3
> > dvdb=3D# select * from unpivot;
> >  intcol | floatcol | strcol
> > --------+----------+--------
> >       1 |      1.1 | one
> >       2 |      2.2 | two
> >       3 |      3.3 | three
> > (3 rows)
> > I want 9 records returned, each row with 3 cols, 1st col is the ctid,
> > second is the column name, third is the val.
>
> I think it should work:
>
> select u.ctid, e.* from unpivot u, to_jsonb(u) j, jsonb_each_text(j) e;
>
> Best regards,
>
> depesz
>
>

--000000000000f27ede0642b4186b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Wow !<br>That did it, even with=C2=A0the predicate I stuck=
 on the end when I tried it.<br><br>Thanks Depesz !</div><br><div class=3D"=
gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On=
 Mon, Nov 3, 2025 at 12:22=E2=80=AFPM hubert depesz lubaczewski &lt;<a href=
=3D"mailto:depesz@depesz.com">depesz@depesz.com</a>&gt; wrote:<br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex">On Mon, Nov 03, 2025 at 12:1=
8:55PM -0500, dfgpostgres wrote:<br>
&gt; psql (13.2, server 15.3) on linux<br>
&gt; <br>
&gt; I think they call this &quot;unpivot&quot; in MSSQL ?<br>
&gt; <br>
&gt; How can I get an sql query to return=C2=A0 one line per column with...=
 an ID,<br>
&gt; column name and value.=C2=A0 the ctid for the id field is fine.<br>
&gt; <br>
&gt; Example:<br>
&gt; dvdb=3D# create table unpivot (intcol integer, floatcol float, strcol<=
br>
&gt; varchar);<br>
&gt; CREATE TABLE<br>
&gt; dvdb=3D# insert into unpivot (intcol,floatcol,strcol) values<br>
&gt; (1,1.1,&#39;one&#39;),(2,2.2,&#39;two&#39;),(3,3.3,&#39;three&#39;);<b=
r>
&gt; INSERT 0 3<br>
&gt; dvdb=3D# select * from unpivot;<br>
&gt;=C2=A0 intcol | floatcol | strcol<br>
&gt; --------+----------+--------<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A01 |=C2=A0 =C2=A0 =C2=A0 1.1 | one<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A02 |=C2=A0 =C2=A0 =C2=A0 2.2 | two<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A03 |=C2=A0 =C2=A0 =C2=A0 3.3 | three<br>
&gt; (3 rows)<br>
&gt; I want 9 records returned, each row with 3 cols, 1st col is the ctid,<=
br>
&gt; second is the column name, third is the val.<br>
<br>
I think it should work:<br>
<br>
select u.ctid, e.* from unpivot u, to_jsonb(u) j, jsonb_each_text(j) e;<br>
<br>
Best regards,<br>
<br>
depesz<br>
<br>
</blockquote></div>

--000000000000f27ede0642b4186b--