samedi 31 octobre 2020

MySQL pivot row into columns with in columns(using concat)

I have one MySQL tables:(how to use concat with in if(condition)) table :

CREATE TABLE `brute20` (
  `record_id` int(10) UNSIGNED NOT NULL,
  `mesure_id` int(10) UNSIGNED NOT NULL,
  `J_DATE` date NOT NULL,
  `J_TIME` time NOT NULL,
  `Value` varchar(10) NOT NULL,
   `qulity_code` char(1) NOT NULL,
   `added_date_time` datetime NOT NULL,
  `updated_last_column` datetime NOT NULL
);

I would like to get a table in the rows and columns as mesure_id. So far I was unable to get an output

Using this query:

set @var = null;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      **'sum(IF(mesure_id =', mesure_id,' , concat(Value,qulity_code), null)) as ','`',mesure_id,'`'**
    )
  ) INTO @var
 FROM brute20  where mesure_id IN ('1201','1203','1202');

-- select @var;


SET @var = CONCAT(' select J_DATE, J_TIME,  ',  @var, 
  ' FROM
  brute20  group by J_TIME ');
  
  -- select @var;
  
  PREPARE stmt FROM @var;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

but I would like to have instead something like:

enter image description here

The problem is that I cannot show correct value in mesure_id

Aucun commentaire:

Enregistrer un commentaire