Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / PHP
Tip/Trick

SQL for Listing Field(s) on Columns

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
11 Jan 2022CPOL4 min read 6.4K   72   7   2
A tip to get a data list on 2 (or more) columns.
This tip shows how to obtain a two or three columns list of an ordered field of a table using only SQL statements. The items can be placed by row (e.g., left to right) or by column.

Introduction

I had the problem of getting an ordered list of data on two or three columns using only SQL statements obviously immersed in a programming language (PHP) to visualize the results.

Background

The reader should have a basic understanding of SQL (and possibly PHP); the development is based on some capabilities of the database, in particular Temporary tables, Windowing functions and Join Left options; see Wikipedia Comparison of relational database management systems for databases that support those options.

From Data to Columns

This was obtained by creating a temporary table that, in addition to the field in question, has two other fields of which the first contains a progressive number which will be subsequently modified so that exists at most two or three rows with the same number, the second is used to identify the column where the field will be placed.

Columns Ordered Left to Right

The SQL below creates an ordered table over one field:

SQL
CREATE TEMPORARY TABLE multiCols AS 
   SELECT ROW_NUMBER () OVER (ORDER BY FieldName) RowNum, 
   '0' PlaceHolder,
   FieldName 
   FROM TableName [possible WHERE clause and GROUP BY FieldName clause]

ROW_NUMBER is one of the so-called window functions that assigns a sequential integer to each row of a query’s result set.

An effective example is below:

SQL
CREATE TEMPORARY TABLE multiCols AS 
  SELECT ROW_NUMBER () OVER (ORDER BY Name) RowNum, 
  '0' PlaceHolder, Name 
  FROM DrugsHandBook WHERE Category IS NULL OR Category = '';

After the creation of the temporary table, we must modify the fields RowNum and PlaceHolder:

SQL
UPDATE multiCols SET PlaceHolder = ((RowNum-1) % colsNumber)
   RowNum = Rownum - ((RowNum-1) % colsNumber)

Here colsNumber can be 2 or 3, therefore in case of two columns, the below UPDATE statement is more efficient and performing.

SQL
UPDATE multiCols SET RowNum = Rownum - 1,PlaceHolder = 1 WHERE (RowNum %2) = 0

After the UPDATE, there are a couple (or a triple) of rows with the same RowNumber and the placeholder having values 0, 1 (and 2) respectively.

Lastly by the SQL below, we obtain the data on two or three columns[1]:

SQL
SELECT A.FieldName,BFieldName[,C.FieldName] _
FROM (SELECT * FROM multiCols WHERE PlaceHolder = 0) A
    LEFT JOIN (SELECT * FROM multiCols WHERE PlaceHolder = 1) B
           ON A.RowNum = B.RowNum
    LEFT JOIN (SELECT * FROM multiCols WHERE PlaceHolder = 2) C
       ON A.RowNum = C.RowNum;

For two columns, the last LEFT JOIN and C.FieldName are omitted.

Columns Ordered Up to Bottom

In this case, the SQL for create a table is a little more complicated for we need to know how many rows are interested:

SQL
CREATE TEMPORARY TABLE multiCols AS SELECT LAST_VALUE (RowNum) OVER (ORDER BY RowNum
  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Count,
  0 PlaceHolder,RowNum,FieldName 
  FROM (SELECT ROW_NUMBER () OVER (ORDER BY FieldName) RowNum,FieldName FROM TableName
  [possible WHERE clause and GROUP BY FieldName clause])

For understanding, note that the last line it is almost similar to the creation of the table for the left right order and in the preceding lines, the LAST_VALUE window function captures the last RowNum of the set (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), furthermore there is the field Count which contains the number of rows.

In order to deals only in integer division[2] the below UPDATE statement sets the Count field to the next value multiple of two (or three):

SQL
UPDATE multiCols SET Count = Count + colsNumber - (Count % colsNumber)
  WHERE (Count % colsNumber) > 0

To obtain the rows coupling and the correct PlaceHolder values, the next statement CREATE one (or two) others TEMPORARY TABLE[3]:

SQL
CREATE TEMPORARY TABLE multiCols2 
  AS SELECT 1 PlaceHolder,Count,FieldName,Rownum - Count / colsNumber AS RowNum 
  FROM multiCols WHERE Rownum > Count /  colsNumber
CREATE TEMPORARY TABLE multiCols3 
  AS SELECT 2 PlaceHolder,FieldName,Rownum – Count / colsNumber AS RowNum 
  FROM multiCols2 WHERE Rownum > Count / colsNumber 

And the result is obtained, for three columns, by:

SQL
SELECT A.FieldName,B.FieldName,C.FieldName 
  FROM (SELECT * FROM multiCols WHERE PlaceHolder = 0 AND RowNum <= Count/3) A
  LEFT JOIN multiCols2 B ON A.RowNum = B.RowNum 
  LEFT JOIN multiCols3 C ON A.RowNum = C.RowNum

Deals With Multi Fields

It is not complicated to show multiple fields: these must be indicated when creating the table like in the example below where the second field is also in the ORDER clause.

SQL
CREATE TEMPORARY TABLE multiCols AS
    SELECT ROW_NUMBER () OVER (ORDER BY Autore,Citazione) RowNum,
    0 PlaceHolder,Autore,Citazione FROM Citazioni;
UPDATE multiCols SET PlaceHolder = ((RowNum-1) % 3),RowNum = Rownum - ((RowNum-1) % 3);
SELECT A.Autore,A.Citazione,B.Autore,B.Citazione,C.Autore,B.Citazione 
    FROM (SELECT * FROM multiCols WHERE PlaceHolder = 0) A 
    LEFT JOIN (SELECT * FROM multiCols WHERE PlaceHolder = 1) B ON A.RowNum = B.RowNum 
    LEFT JOIN (SELECT * FROM multiCols WHERE PlaceHolder = 2) C ON A.RowNum = C.RowNum;

Using the Code: The PHP Script

The script aims to create a list on two or three columns simply providing table and field(s) name and possibly WHERE or/and GROUP clause; data are retrieved by PDO object.
The PHP multiColumns.php script contains the function multiCols that returns an array of data:

SQL
multiCols($dbh,$parms,$Direction = "H",$Cols = 2,$Trace = "off")

Where:

  • $dbh is the handle of the database;
  • $parms is an array of parameters (see below);
  • $Direction is a direction, i.e., by line (H) the default or by column (V)
  • $Cols the number of columns: 2, the default, or 3;
  • $Trace when on shows the SQL statements.

$parms can contain:

  • Table: mandatory, table name;
  • Order: example Product COLLATE NOCASE, Price DESC;
  • Fields: a list of fields to show example: Author, Citation;
  • Condition: a WHERE or GROUP BY clause;
  • MySql: true or false (default).

The function returns a two dimensional array:

If Cols is the number of required columns and nFields is the number of fields retrieved:

  1. Array with the number of rows extracted (countRows) and the fields name repeated as many times as required columns
  2. Array of dimension Ceil(countRows / Cols) x nFields * Cols

The script has been tested on SQLite version 3.31.0, PostgreSQL 14.1 and MySql 8.0.27.

Output Example

Click to enlarge image

The above image has been generated by the below SQLs.

Image 2

Note

  1. ^ For MySql, there are some differences:
    It is not possible to refer to a TEMPORARY table more than once in the same query, for this, the final SELECT statement needs two (or three) TEMPORARY table(s) so, after the CREATE TABLE and UPDATE statements, SQLs are:
    SQL
    CREATE TEMPORARY TABLE multiCols2 AS SELECT * FROM multiCols WHERE PlaceHolder = 1
    CREATE TEMPORARY TABLE multiCols3 AS SELECT * FROM multiCols WHERE PlaceHolder = 2
    SELECT  A.fieldName,B.fieldName,C.fieldName _
            FROM (SELECT * FROM multiCols WHERE PlaceHolder = 0) A
        LEFT JOIN multiCols2 B ON A.RowNum = B.RowNum LEFT JOIN multiCols3 C 
          ON A.RowNum = C.RowNum
  2. ^ This is due to the different treatment of the division between integers, for example SQLite and PostgreSQL return an integer, MySql returns decimals.
  3. ^ With this solution, all SQL statements are compatible MySql.

History

  • 11th January, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Condor Informatique
Italy Italy
Computer literacy (software) : Languages: PHP, Javascript, SQL Autoit,Basic4Android; Frameworks: JOOMLA!
Teaching/Training skills on Office, WEB site development and programming languages.
Others : WEB site development.
UNDP Missions
feb – may 2003 Congo DR Bukavu: ground IT computer course
nov 2003 Burundi Bujumbura: Oracle Data Base course
feb 2005 Burundi Bujumbura: JAVA course
mar 2005 Mali Kati: MS Office course
oct 2006 Mali Kati: MS Office course
jun 2006 Burkina Faso Bobo Dioulasso: MS Office course
jun 2007 Burkina Faso Bobo Dioulasso: MS Office course
may 2007 Argentina Olavarria hospital: Internet application for access to medical records
apr 2008 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
jun 2008 Niger Niamey: analysis of the computing needs of the Niamey hospital
may 2009 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
oct 2010 Niger Niamey: analysis of the computing needs of the Niamey hospital (following)
Region Piedmont project Evaluation
mar 2006 Burkina Faso, Niger
mar 2007 Benin, Burkina Faso, Niger
sep 2008 Benin, Burkina Faso, Niger
Others
feb 2010 Burundi Kiremba hospital: MS Office course
feb 2011 Congo DR Kampene hospital: MS Office course

Comments and Discussions

 
QuestionReally just use SQL for this Pin
Andreas Weiden13-Jan-22 8:03
Andreas Weiden13-Jan-22 8:03 
There is no need for any Temporary tables. You could do it with just a single sql

SELECT
    MAX(col1),
    MAX(col2),
    MAX(col3),
    MAX(col4)
FROM
    (
        SELECT
            grp,
            CASE
                WHEN rn = 1 THEN
                    author
                ELSE
                    NULL
            END  col1,
            CASE
                WHEN rn = 0 THEN
                    author
                ELSE
                    NULL
            END  col3,
            CASE
                WHEN rn = 1 THEN
                    citazione
                ELSE
                    NULL
            END  col2,
            CASE
                WHEN rn = 0 THEN
                    citazione
                ELSE
                    NULL
            END  col4
        FROM
            (
                SELECT
                    trunc((rn + 1) / 2)       grp,
                    mod(rn, 2)                rn,
                    author,
                    citazione
                FROM
                    (
                        SELECT
                            ROW_NUMBER() OVER (ORDER BY view_name) rn,
                            author,
                            citazione
                        FROM
                            yourtable
                    )
            )
    )
GROUP BY
    grp

Andreas

AnswerRe: Really just use SQL for this Pin
Member 420697413-Jan-22 8:52
Member 420697413-Jan-22 8:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.