Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (sl_no int, username text, designation text, salary int);
	
INSERT INTO tbl VALUES
  (1, 'A', 'XYZ', 10000)
, (2, 'B', 'RTS', 50000)
, (3, 'C', 'QWE', 20000)
, (4, 'D', 'HGD', 34343)
;

CREATE TABLE



INSERT 0 4


-- Postgres 9.4
-- new (advanced)
SELECT 'SELECT *
FROM   crosstab(
       ''SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM ' || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname) || '::text', ',') || '])
                WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2''
   ) t (attnum int, '
     || (SELECT string_agg('r'|| rn ||' text', ',')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;


sql


SELECT *
FROM   crosstab(
       'SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM tbl) t
             , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text])
                WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2'
   ) t (attnum int, r1 text,r2 text,r3 text,r4 text)



SELECT 1


-- old (simple)
SELECT 'SELECT * FROM unnest(
  ''{sl_no, username, designation, salary}''::text[]
, ' || string_agg(quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
              || '::text[]', E'\n, ')
    || E') \n AS t(col,' || string_agg('row' || sl_no, ',') || ')' AS pg94
FROM   tbl;


pg94


SELECT * FROM unnest(
  '{sl_no, username, designation, salary}'::text[]
, '{1,A,XYZ,10000}'::text[]
, '{2,B,RTS,50000}'::text[]
, '{3,C,QWE,20000}'::text[]
, '{4,D,HGD,34343}'::text[]) 
 AS t(col,row1,row2,row3,row4)



SELECT 1


-- execute result of above query
SELECT unnest('{sl_no, username, designation, salary}'::text[]) AS col, unnest('{1,A,XYZ,10000}'::text[]) AS row1, unnest('{2,B,RTS,50000}'::text[]) AS row2, unnest('{3,C,QWE,20000}'::text[]) AS row3, unnest('{4,D,HGD,34343}'::text[]) AS row4;


col
row1
row2
row3
row4


sl_no
1
2
3
4

username
A
B
C
D

designation
XYZ
RTS
QWE
HGD

salary
10000
50000
20000
34343



SELECT 4


-- Postgres 9.3
-- new (advanced)
SELECT 'SELECT *
FROM   crosstab(
       ''SELECT unnest(''' || quote_literal(array_agg(attname)) || '''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[' || string_agg(quote_ident(attname) || '::text', ',') || ']) AS val
        FROM   ' || attrelid::regclass || '
        ORDER  BY generate_series(1,' || count(*) || '), 2''
   ) t (col text, '
     || (SELECT string_agg('r'|| rn ||' text', ',') FROM (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;


sql


SELECT *
FROM   crosstab(
       'SELECT unnest(''{sl_no,username,designation,salary}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) AS val
        FROM   tbl
        ORDER  BY generate_series(1,4), 2'
   ) t (col text, r1 text,r2 text,r3 text,r4 text)



SELECT 1


-- old (simple)
SELECT 'SELECT unnest(''{sl_no, username, designation, salary}''::text[] AS col)
     , ' || string_agg('unnest('
                    || quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
                    || '::text[]) AS row' || sl_no, E'\n     , ') AS pg93
FROM   tbl;


pg93


SELECT unnest('{sl_no, username, designation, salary}'::text[] AS col)
     , unnest('{1,A,XYZ,10000}'::text[]) AS row1
     , unnest('{2,B,RTS,50000}'::text[]) AS row2
     , unnest('{3,C,QWE,20000}'::text[]) AS row3
     , unnest('{4,D,HGD,34343}'::text[]) AS row4



SELECT 1
col	row1	row2	row3	row4
sl_no	1	2	3	4
username	A	B	C	D
designation	XYZ	RTS	QWE	HGD
salary	10000	50000	20000	34343