MySQL - select multiple rows from one table into one result row-CodePudding

I have a table where are periodically imported values. For statistics use I need to create diff view of two dates.

Table looks like

name	cpu_count	memory_count	import_date
alpha	2	4	2022-09-28
alpha	2	6	2022-10-01

and I need select columns like name, cpu_old, memory_old, cpu_new, memory_new into one row.

name	cpu_old	memory_old	cpu_new	memory_new
alpha	2	4	2	6

Can anybody help me? I was trying it with UNION, GROUP etc but every time I've get two rows.

CodePudding user response：

SOLUTION 1

You have data something like this:

SQL for create table:

CREATE TABLE `my_cpu` (
  `name` varchar(32) NOT NULL,
  `cpu_count` tinyint(4) DEFAULT NULL,
  `memory_count` tinyint(4) DEFAULT NULL,
  `import_date` date NOT NULL,
  PRIMARY KEY (`name`,`import_date`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii

RESULT:

SQL for create that result:

SELECT a.name,
    a.cpu_count AS cpu_old,
    a.memory_count AS memory_old,
    b.cpu_count AS cpu_new  ,
    b.memory_count AS memory_new
FROM `my_cpu`   a INNER JOIN `my_cpu` b ON a.name = b.name
WHERE a.import_date = "2022-09-28" AND b.import_date = "2022-10-01"

EXPLAINATION:

Because you store data every week, so you have 2 dates.
The data is saved in one table_names, lets say it "my_cpu".
Create self join from my_cpu. So you have 2 table (a and b). With condition a.name = b.name
Filter where a.import_date = your_old_date and b.import_date = your_new_date

FURTHER READ:

RESULT:

SQL:

SELECT  `name`,

    SUBSTRING_INDEX(SUBSTRING_INDEX(cpu_count,",",2),",",-1) cpu_old,
    SUBSTRING_INDEX(SUBSTRING_INDEX(memory_count,",",2),",",-1) memory_old,
    
    SUBSTRING_INDEX(cpu_count,",",1) cpu_new,
    SUBSTRING_INDEX(memory_count,",",1) memory_new
FROM
(

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME
)

AS dbx

EXPLAINATION:

Create query for get cpu and memory in ordered by import_date in descending each cpu_name

THE QUERY:

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME

WOULD REPRODUCE:

Search with substring_index,

The lastet would be the first letter before (,)

The second(oldest) would be the the second letter after the (,)

CodePudding user response：

@sukalogika Thank you. First solution is expected behaviour.