1

I have a table shown below.

CREATE TABLE tbl (sl_no int, username text, petname1 text, petname2 text);

INSERT INTO tbl VALUES
  (1, 'A', 'XYZ', 'ABC')
, (2, 'B', 'RTS', 'DEF')
, (3, 'C', 'QWE', 'GHI')
, (4, 'D', 'HGD', 'JKL')
;

How do I convert a single column to multiple row entries? into this result:

1, 'A', 'XYZ'
1, 'A', 'ABC'
2, 'B', 'RTS'
2, 'B', 'DEF'
3, 'C', 'QWE'
3, 'C', 'GHI'
4, 'D', 'HGD'
4, 'D', 'JKL'

using postgresql 11.3 if possible

Thompson Sanjoto
  • 13
  • 3

2 Answers2

3

You can do this without querying the table a second time, using an unpivot. The easiest way to do that is with CROSS JOIN LATERAL (VALUES

SELECT
  t.sl_no,
  t.username,
  v.petname
FROM tbl t
CROSS JOIN LATERAL (VALUES
    (petname1),
    (petname2)
) v(petname);

dotnetfiddle

Charlieface
  • 17,078
  • 22
  • 44
0
select * from (
select sl_no, username, petname1 from tbl
union all
select sl_no, username, petname2 from tbl
) t order by 1
mustaccio
  • 28,207
  • 24
  • 60
  • 76