Group records with same value in one row informix

Question

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.

Answer 1

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]]

or

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