MIME-Version: 1.0
From: dfgpostgres <dfgpostgres3@gmail.com>
Date: Mon, 3 Nov 2025 12:18:55 -0500
Message-ID: <CAAcmDX_KU4A1DWzKmO4XacVQ72+0KZ9rZSvQwHkCWgpqB-Lb2w@mail.gmail.com>
Subject: PG Unpivot ?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a3d9f30642b3e9f0"
Archived-At: <https://www.postgresql.org/message-id/CAAcmDX_KU4A1DWzKmO4XacVQ72%2B0KZ9rZSvQwHkCWgpqB-Lb2w%40mail.gmail.com>
Precedence: bulk

--000000000000a3d9f30642b3e9f0
Content-Type: text/plain; charset="UTF-8"

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=# create table unpivot (intcol integer, floatcol float, strcol
varchar);
CREATE TABLE
dvdb=# insert into unpivot (intcol,floatcol,strcol) values
(1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three');
INSERT 0 3
dvdb=# 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.

Thanks in Advance !

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

<div dir=3D"ltr">psql (13.2, server 15.3) on linux<div><br></div><div>I thi=
nk they call this &quot;unpivot&quot; in MSSQL ?</div><div><br></div><div>H=
ow can I get an sql query to return=C2=A0 one line per column with... an ID=
, column name and value.=C2=A0 the ctid for the id field is fine.=C2=A0=C2=
=A0</div><div><br></div><div>Example:</div><div>dvdb=3D# create table unpiv=
ot (intcol integer, floatcol float, strcol varchar);<br>CREATE TABLE<br>dvd=
b=3D# insert into unpivot (intcol,floatcol,strcol) values (1,1.1,&#39;one&#=
39;),(2,2.2,&#39;two&#39;),(3,3.3,&#39;three&#39;);<br>INSERT 0 3<br>dvdb=
=3D# select * from unpivot;<br>=C2=A0intcol | floatcol | strcol <br>-------=
-+----------+--------<br>=C2=A0 =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 =C2=A01.1 |=
 one<br>=C2=A0 =C2=A0 =C2=A0 2 | =C2=A0 =C2=A0 =C2=A02.2 | two<br>=C2=A0 =
=C2=A0 =C2=A0 3 | =C2=A0 =C2=A0 =C2=A03.3 | three<br>(3 rows)<br></div><div=
><br></div><div>I want 9 records returned, each row with 3 cols, 1st col is=
 the ctid, second is the column name, third is the val.</div><div><br></div=
><div>Thanks in Advance !</div><div><br></div></div>

--000000000000a3d9f30642b3e9f0--