How do I convert a single column into multiple rows entries in postgresSQL

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

select * from (
select sl_no, username, petname1 from tbl
union all
select sl_no, username, petname2 from tbl
) t order by 1

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply