public inbox for [email protected]  

help / color / mirror / Atom feed

PG Unpivot ?
2+ messages / 2 participants
[nested] [flat]

* PG Unpivot ?
@ 2025-11-03 17:18  dfgpostgres <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: dfgpostgres @ 2025-11-03 17:18 UTC (permalink / raw)
  To: [email protected]

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 !


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PG Unpivot ?
@ 2025-11-03 17:33  Tom Lane <[email protected]>
  parent: dfgpostgres <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2025-11-03 17:33 UTC (permalink / raw)
  To: dfgpostgres <[email protected]>; +Cc: [email protected]

dfgpostgres <[email protected]> writes:
> I want 9 records returned, each row with 3 cols, 1st col is the ctid,
> second is the column name, third is the val.

Perhaps psql's "expanded" mode is close enough?

regression=# \x
Expanded display is on.
regression=# select * from unpivot;
-[ RECORD 1 ]---
intcol   | 1
floatcol | 1.1
strcol   | one
-[ RECORD 2 ]---
intcol   | 2
floatcol | 2.2
strcol   | two
-[ RECORD 3 ]---
intcol   | 3
floatcol | 3.3
strcol   | three

PS: I do not advise relying on ctid as a row identifier.
Use a proper primary key.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-11-03 17:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-03 17:18 PG Unpivot ? dfgpostgres <[email protected]>
2025-11-03 17:33 ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox