Returning data in multiple columns

I was working today on some updates to client database. While doing it, I figured it would be simpler if I saw all “codenames" and ids of rows from dictionary table – not so big. But it was bigger than my screen – I have only 90 lines of text on screen, and there were ~ 200 rows of data in the table. So I started thinking – how to show this (codename, id) into more than one column, in psql.

First, let's make some simple sample table/data. We don't need 200 rows as I just want to show the mechanics.

So, let's say I will do:

$ CREATE TABLE test (id serial PRIMARY KEY, codename text NOT NULL UNIQUE);
CREATE TABLE
 
$ copy test (codename) FROM stdin;
shareholders
hollering
witchery
chameleon
revoking
murderess
auditing
confronts
pardons
wiener
pendulums
weaving
piteously
password
outlawed
taxis
dignities
croquettes
individualizing
chide
\.
COPY 20

Obviously, when I just select the data, I will see 20 rows of data, summary, header and separator:

SELECT id, codename FROM test ORDER BY id;
 id |    codename     
----+-----------------
  1 | shareholders
  2 | hollering
  3 | witchery
  4 | chameleon
  5 | revoking
  6 | murderess
  7 | auditing
  8 | confronts
  9 | pardons
 10 | wiener
 11 | pendulums
 12 | weaving
 13 | piteously
 14 | password
 15 | outlawed
 16 | taxis
 17 | dignities
 18 | croquettes
 19 | individualizing
 20 | chide
(20 ROWS)

How could we make it show the same data, but in more than one column?

Well, I could do some awk trickery:

$ \o | awk 'NR < 3 || 0 == NR % 2 {print $0} NR>2 && 1 == NR%2 {printf "%s", $0}'
 
$ SELECT id, codename FROM test ORDER BY id;
 id |    codename
----+-----------------
  1 | shareholders  2 | hollering
  3 | witchery  4 | chameleon
  5 | revoking  6 | murderess
  7 | auditing  8 | confronts
  9 | pardons 10 | wiener
 11 | pendulums 12 | weaving
 13 | piteously 14 | password
 15 | outlawed 16 | taxis
 17 | dignities 18 | croquettes
 19 | individualizing 20 | chide
(20 ROWS)
 
$ \o

While it kinda worked, it wasn't really nice – the columns are not aligned. But maybe I can do it in plain SQL?

First, let's try to add basic information – which column given row should go to. To make things interesting – let's go into 3 column layout:

$ SELECT
    id,
    codename,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) % 3 AS column_number,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) / 3 AS ROW_NUMBER
FROM
    test
ORDER BY
    codename
;
 id |    codename     | column_number | ROW_NUMBER 
----+-----------------+---------------+------------
  7 | auditing        |             0 |          0
  4 | chameleon       |             1 |          0
 20 | chide           |             2 |          0
  8 | confronts       |             0 |          1
 18 | croquettes      |             1 |          1
 17 | dignities       |             2 |          1
  2 | hollering       |             0 |          2
 19 | individualizing |             1 |          2
  6 | murderess       |             2 |          2
 15 | outlawed        |             0 |          3
  9 | pardons         |             1 |          3
 14 | password        |             2 |          3
 11 | pendulums       |             0 |          4
 13 | piteously       |             1 |          4
  5 | revoking        |             2 |          4
  1 | shareholders    |             0 |          5
 16 | taxis           |             1 |          5
 12 | weaving         |             2 |          5
 10 | wiener          |             0 |          6
  3 | witchery        |             1 |          6
(20 ROWS)

Now, this is something we can build on.

Let's use it as CTE, and decrease number of rows (using group by), and add new columns with case…when…else…end:

WITH numbered AS (
SELECT
    id,
    codename,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) % 3 AS column_number,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) / 3 AS ROW_NUMBER
FROM
    test
)
SELECT
    MIN( CASE WHEN column_number = 0 THEN id ELSE NULL END ) AS id_1,
    MIN( CASE WHEN column_number = 0 THEN codename ELSE NULL END ) AS codename_1,
    MIN( CASE WHEN column_number = 1 THEN id ELSE NULL END ) AS id_2,
    MIN( CASE WHEN column_number = 1 THEN codename ELSE NULL END ) AS codename_2,
    MIN( CASE WHEN column_number = 2 THEN id ELSE NULL END ) AS id_3,
    MIN( CASE WHEN column_number = 2 THEN codename ELSE NULL END ) AS codename_3
FROM numbered
GROUP BY ROW_NUMBER
ORDER BY ROW_NUMBER;
 id_1 |  codename_1  | id_2 |   codename_2    |  id_3  | codename_3 
------+--------------+------+-----------------+--------+------------
    7 | auditing     |    4 | chameleon       |     20 | chide
    8 | confronts    |   18 | croquettes      |     17 | dignities
    2 | hollering    |   19 | individualizing |      6 | murderess
   15 | outlawed     |    9 | pardons         |     14 | password
   11 | pendulums    |   13 | piteously       |      5 | revoking
    1 | shareholders |   16 | taxis           |     12 | weaving
   10 | wiener       |    3 | witchery        | [NULL] | [NULL]
(7 ROWS)

Kinda-nice. I mean – I got what I wanted, but the query is not really beautiful. It would be so great to get this kind of formatting help in psql itself 🙂

While I'm at this – it struck me – it's all nice, but I'd prefer to see it oriented the other way around – i.e. first fill first column, and then switch to next. This will require some heavier magic.

$ WITH base_info AS (
    SELECT id, codename FROM test
),
math AS (
    SELECT
        COUNT(*),
        3::int4 AS need_columns,
        CEIL(COUNT(*)::float8 / 3.0)::int4 AS need_rows
    FROM
        base_info
)
SELECT * FROM math;
 COUNT | need_columns | need_rows 
-------+--------------+-----------
    20 |            3 |         7
(1 ROW)

This shows simple calculation, based on number of rows, how many rows we need for given number of columns. With this in place, I can use it to get “column_number" and “row_number" calculated:

WITH base_info AS (
    SELECT id, codename FROM test
),
math AS (
    SELECT
        COUNT(*),
        3::int4 AS need_columns,
        CEIL(COUNT(*)::float8 / 3.0)::int4 AS need_rows
    FROM
        base_info
),
numbered AS (
SELECT
    id,
    codename,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) / need_rows AS column_number,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) % need_rows AS ROW_NUMBER
FROM
    base_info,
    math
)
SELECT
    MIN( CASE WHEN column_number = 0 THEN id ELSE NULL END ) AS id_1,
    MIN( CASE WHEN column_number = 0 THEN codename ELSE NULL END ) AS codename_1,
    MIN( CASE WHEN column_number = 1 THEN id ELSE NULL END ) AS id_2,
    MIN( CASE WHEN column_number = 1 THEN codename ELSE NULL END ) AS codename_2,
    MIN( CASE WHEN column_number = 2 THEN id ELSE NULL END ) AS id_3,
    MIN( CASE WHEN column_number = 2 THEN codename ELSE NULL END ) AS codename_3
FROM numbered
GROUP BY ROW_NUMBER
ORDER BY ROW_NUMBER;
 id_1 | codename_1 | id_2 |   codename_2    |  id_3  |  codename_3  
------+------------+------+-----------------+--------+--------------
    7 | auditing   |   19 | individualizing |      5 | revoking
    4 | chameleon  |    6 | murderess       |      1 | shareholders
   20 | chide      |   15 | outlawed        |     16 | taxis
    8 | confronts  |    9 | pardons         |     12 | weaving
   18 | croquettes |   14 | password        |     10 | wiener
   17 | dignities  |   11 | pendulums       |      3 | witchery
    2 | hollering  |   13 | piteously       | [NULL] | [NULL]
(7 ROWS)

Note: the final query, after all the ctes – is the same as in previous example – it's just using group/min/case to convert values into grid based on their column_number/row_number.

We can also remove the math cte, and just do some math in base_info:

WITH base_info AS (
    SELECT
        id,
        codename,
        CEIL((COUNT(*) OVER ())::float8 / 3.0)::int4 AS need_rows
    FROM test
),
numbered AS (
SELECT
    id,
    codename,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) / need_rows AS column_number,
    (ROW_NUMBER() OVER (ORDER BY codename) - 1 ) % need_rows AS ROW_NUMBER
FROM
    base_info
)
SELECT
    MIN( CASE WHEN column_number = 0 THEN id ELSE NULL END ) AS id_1,
    MIN( CASE WHEN column_number = 0 THEN codename ELSE NULL END ) AS codename_1,
    MIN( CASE WHEN column_number = 1 THEN id ELSE NULL END ) AS id_2,
    MIN( CASE WHEN column_number = 1 THEN codename ELSE NULL END ) AS codename_2,
    MIN( CASE WHEN column_number = 2 THEN id ELSE NULL END ) AS id_3,
    MIN( CASE WHEN column_number = 2 THEN codename ELSE NULL END ) AS codename_3
FROM numbered
GROUP BY ROW_NUMBER
ORDER BY ROW_NUMBER;

But it's not all that different.

Anyway, I have the feeling that it can be done in a simpler way, but I'm out of ideas. For now at least. If you can do it better – please share your approach.

5 thoughts on “Returning data in multiple columns” 

  2. $ export PAGER=column; psql analytics
 Timing is on.
 psql (9.4.0)
 Type "help" for help.

    Neat feature. Never knew about that one.

     [local]:ubuntu@analytics# select * from test;
 id |    codename        24 | chameleon          29 | pardons            34 | password           39 | individualizing
 ----+-----------------   25 | revoking           30 | wiener             35 | outlawed           40 | chide
  21 | shareholders       26 | murderess          31 | pendulums          36 | taxis             (20 rows)
  22 | hollering          27 | auditing           32 | weaving            37 | dignities
  23 | witchery           28 | confronts          33 | piteously          38 | croquettes

    (hope that formatted well, feel free to edit if it didn’t)

  3. The problem with column is that it will break when codenames (well, any of the columns) will contain spaces.

  5. I think that you already made a main thing – transform original select statement to multi column output.
    Now we can write psql script to automatically made this transformation.
    Below script and it’s output.

    postgres=# \i mc.sql
SELECT
    col1: id
    col2: codename
    FROM: test
NUMBER OF COLUMNS: 3
 
 id_1 |  codename_1  | id_2 |   codename_2    | id_3 | codename_3
------+--------------+------+-----------------+------+------------
    7 | auditing     |    4 | chameleon       |   20 | chide
    8 | confronts    |   18 | croquettes      |   17 | dignities
    2 | hollering    |   19 | individualizing |    6 | murderess
   15 | outlawed     |    9 | pardons         |   14 | password
   11 | pendulums    |   13 | piteously       |    5 | revoking
    1 | shareholders |   16 | taxis           |   12 | weaving
   10 | wiener       |    3 | witchery        |      |
(7 ROWS)
    postgres=# \i mc.sql
SELECT
    col1: reltype
    col2: relname
    FROM: pg_class WHERE relname LIKE 'pg_stat%'
NUMBER OF COLUMNS: 2
 
 reltype_1 |            relname_1             | reltype_2 |         relname_2
-----------+----------------------------------+-----------+----------------------------
     11187 | pg_stat_activity                 |     11157 | pg_stat_all_indexes
     11125 | pg_stat_all_tables               |     11207 | pg_stat_bgwriter
     11193 | pg_stat_database                 |     11196 | pg_stat_database_conflicts
     11190 | pg_stat_replication              |     11161 | pg_stat_sys_indexes
     11133 | pg_stat_sys_tables               |     11199 | pg_stat_user_functions
     11164 | pg_stat_user_indexes             |     11140 | pg_stat_user_tables
     11129 | pg_stat_xact_all_tables          |     11137 | pg_stat_xact_sys_tables
     11203 | pg_stat_xact_user_functions      |     11144 | pg_stat_xact_user_tables
     11167 | pg_statio_all_indexes            |     11177 | pg_statio_all_sequences
     11147 | pg_statio_all_tables             |     11171 | pg_statio_sys_indexes
     11181 | pg_statio_sys_sequences          |     11151 | pg_statio_sys_tables
     11174 | pg_statio_user_indexes           |     11184 | pg_statio_user_sequences
     11154 | pg_statio_user_tables            |     10818 | pg_statistic
         0 | pg_statistic_relid_att_inh_index |     11087 | pg_stats
(14 ROWS)
    /* _____ Start of mc.sql _____ */
/* RETURNING DATA IN MULTIPLE COLUMNS */
 
\echo SELECT
\prompt '    col1: ' col1
\prompt '    col2: ' col2
\prompt '    from: ' FROM
\prompt 'number of columns: ' n
\echo
 
/* Generate first part of select statement */
SELECT
'with numbered as ( ' ||
'select ' ||
   :'col1'  || ', ' ||
   :'col2'  ||
'  ,(row_number() over (order by ' ||:'col2'||') - 1 ) % '|| :n || ' as column_number' ||
'  ,(row_number() over (order by ' ||:'col2'||') - 1 ) / '|| :n || ' as row_number' ||
' from ' || :'from' || 
') select ' AS select_part1
\gset
 
/* Generate second part of select statement */
WITH t AS (
SELECT ROW_NUMBER() OVER ()::text AS row_num
      ,(ROW_NUMBER() OVER ()-1)::text AS row_num2
  FROM generate_series (1, :n)
)
SELECT string_agg (
'    min( case when column_number = '||row_num2||' then '||:'col1'||' else null end ) as '||:'col1'||'_'||row_num||
'   ,min( case when column_number = '||row_num2||' then '||:'col2'||' else null end ) as '||:'col2'||'_'||row_num
  , ',') AS select_part2
FROM t
\gset
 
/* Generate final part of select statement */
SELECT
'  from numbered' ||
' group by row_number' ||
' order by row_number'  AS select_part3
\gset
 
/* Run all together */
:select_part1 :select_part2 :select_part3
 
 
/* Cleanup */
\unset col1
\unset col2
\unset FROM
\unset n
\unset select_part1
\unset select_part2
\unset select_part3
/* _____ End of mc.sql _____ */

Comments are closed.