Group records with same value in one row informix

informix

I have 3 records in a table below called systables_growth:

tabname         aactn
total_size_kb   32
date_added      2019-08-26 13:00:00

tabname         aactn
total_size_kb   37
date_added      2019-08-27 13:00:00

tabname         aactn
total_size_kb   39
date_added      2019-08-28 13:00:00

SQL server layout:

tabname     total_size_kb   date_added
aactn       32              26/08/2019 13:00
aactn       37              27/08/2019 13:00
aactn       39              28/08/2019 13:00

I want to achieve the desired output below:

tabname                 aactn
2019-08-26 13:00:00     32
2019-08-27 13:00:00     37
2019-08-28 13:00:00     39

SQL server output required:

tabname 26/08/2019 13:00    27/08/2019 13:00    28/08/2019 13:00
aactn   32                  37                  39

Based on the above output wanted. I want to combine records with the same table name and use the date_added value as the column name. I want to try and achieve this using a case statement, but am struggling with the logic. Please note that this is for informix not sql server. I only provided the layout for sql server to make it perhaps easier to understand what I am trying to achieve.

Best Answer

You are actually looking for PIVOT TABLE which seems to not be possible in Informix (I think, just by googling it, I don't know Informix). The CASE workaround, presentet e.g. here would mean that you would need a special CASE for each date... which seems pretty ugly (but you can do it).

What I would recommend, is to query the table in the "tall format" (also called "wide") and do the pivoting (wide format) transformation in the programming language, from which you call the database from. For example, I use R programmiing language a lot, and in R, I use the dcast or acast or cast function from reshape2 library to reshape the data. Just google it for your language:

how to make pivot table in [[whatever-programming-language-you-use]]

convert long table to wide table in [[whatever-programming-language-you-use]]

Related Solutions

Informix – Merge Multiple Table Rows into One Row

There is an answer on Stack Overflow to the question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) which demonstrates how to create a user-defined aggregate in Informix that functions more or less like the GROUP_CONCAT aggregate in MySQL.

You can then easily use this to solve the problem in this question:

SELECT ID, GROUP_CONCAT(Email)
  FROM Anonymous_Table
 GROUP BY ID
 ORDER BY ID;

'Tis curious that both this question and the cross-referenced one have anonymous tables — it is one of the common flaws in questions about databases on the Stack Exchange family of sites.

Retrieve one of two possible values from 1 table in INFORMIX

This answer assumes that your table Ex1 has columns:

key_col — the key column.
flag_col — the flag column, containing L or P (with a check constraint).
data_col — a proxy for the unidentified set of columns containing the other data.
Primary key on key_col and flag_col.

A view like this should work (I didn't say it would work fast):

CREATE VIEW Relevant_Ex1(key_col, flag_col, data_col) AS
    SELECT A.Key_Col, A.Flag_Col, A.Data_Col
      FROM Ex1 AS A
      JOIN (SELECT B.Key_Col, MIN(Flag_Col) AS Flag_Col
              FROM Ex1 AS B
             GROUP BY B.Key_Col
           ) AS C
        ON A.Key_Col = C.Key_Col AND A.Flag_Col = C.Flag_Col;

This uses the fact that the flag column contains either L or P and L sorts before P. So, if there are two rows for a given key, the value L will be selected, else the value P will be selected.

Best Answer

Related Solutions

Informix – Merge Multiple Table Rows into One Row

Retrieve one of two possible values from 1 table in INFORMIX

Related Question