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.
select * from ( select sl_no, username, petname1 from tbl union all select sl_no, username, petname2 from tbl ) t order by 1
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);
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂