Pivoting 2 columns into a rows

Question

I hope one can help me with this issue, I have a list of personnel and each one of them have same/different skills I need someone to help me to create a SQL script to change it I have these two columns

and to pivoted to show the data this way

Thanks for the helpo anyone can provide

Answer 1

For further questions please use text only instead of images.

Based on your data examples which are as follows

create table my_table(
  personnel_id int,
  p_skill  varchar(10) );

insert into my_table values 
(473,'AMR'),(473,'COLL'),(473,'COMM'),(473,'EMER'),
(473,'FRMN'),(473,'JRNM'),(473,'MTRRD'),(474,'MTRRD'),
(475,'JRNM'),(475,'MTRRD'),(476,'COLL'),(476,'JRNM'),(476,'MTRRD');

A simple conditional aggregation will help you

select personnel_id,
       count(case when p_skill ='AMR'   then 1 end) AMR,
       count(case when p_skill ='COLL'  then 1 end) COLL,
       count(case when p_skill ='COMM'  then 1 end) COMM,
       count(case when p_skill ='EMER'  then 1 end) EMER,
       count(case when p_skill ='FRMN'  then 1 end) FRMN,
       count(case when p_skill ='JRNM'  then 1 end) JRNM,
       count(case when p_skill ='MTRRD' then 1 end) MTRRD
from my_table
group by   personnel_id;

Or written differently (only MySQL/MariaDB allows this syntax as far as I know)

select personnel_id,
       sum(p_skill ='AMR' ) AMR,
       sum(p_skill ='COLL' ) COLL,
       sum(p_skill ='COMM' ) COMM,
       sum(p_skill ='EMER' ) EMER,
       sum(p_skill ='FRMN' ) FRMN,
       sum(p_skill ='JRNM' ) JRNM,
       sum(p_skill ='MTRRD' ) MTRRD
from my_table
group by personnel_id;

If you are looking for dynamic pivot use

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('sum(p_skill = ''',p_skill,'''  ) ',p_skill)
      ) INTO @sql
FROM my_table;


SET @sql = CONCAT('SELECT personnel_id, ', @sql, ' 
                   FROM my_table
                   GROUP BY personnel_id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Or

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('count(case when p_skill = ''',p_skill,'''  then 1 end) AS ',p_skill)
      ) INTO @sql
FROM my_table;


SET @sql = CONCAT('SELECT personnel_id, ', @sql, ' 
                   FROM my_table
                   GROUP BY personnel_id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See example

Note, GROUP_CONCAT default has a limit of 1024 characters. See parameter group_concat_max_len if you want to increase.

---

I am using a data set from a server in which we have the personnel ID (close to 1500 techs and each has a minimum of one SKILL). I'm using AZURE Data Studio which runs MSSQL.

SQL Server offers a build in function such as FROM - Using PIVOT and UNPIVOT

SELECT * 
FROM   ( SELECT personnel_id, 
                p_skill
         FROM  my_table
        ) t 
PIVOT( COUNT(p_skill) 
       FOR p_skill IN (AMR,COLL,COMM,EMER,FRMN,JRNM,MTRRD)
      ) AS pivot_table ;

See example