Temporary Table with Person Names Replicated with 12 Times, with Each Row Inserted with a Month Number

Ed L 0
Viewed 14

I would like to create a temporary table that extracts the first name and last name of all profiles from the [Person] table in my SQL database. Then, I want to replicate each person 12 times in this new table and add a column called Month so that each of the 12 replicated rows is inserted with a digit (1-12) to represent a month of the year. Can you tell me how to create a SQL for this?

mysql sql
1 Answers

Using MySQL UNION ALL to create a list of each month repeated for each name.

Repeat the select query once for each month, change the month number for each one:

SELECT name, 1 `month` FROM EMPLOYEE 
  UNION ALL
SELECT name, 2 FROM EMPLOYEE 
  UNION ALL
SELECT name, 3 FROM EMPLOYEE 
  ORDER BY month, name
;

Given EMPLOYEE table:

id name
1 Clark
2 Dave
3 Ava

This outputs:

name month
Ava 1
Clark 1
Dave 1
Ava 2
Clark 2
Dave 2
Ava 3
Clark 3
Dave 3 
-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');
INSERT INTO EMPLOYEE VALUES (4, 'Clark', 'Maintenance');

-- fetch 
SELECT name, 1 `month` FROM EMPLOYEE 
  UNION ALL
SELECT name, 2 FROM EMPLOYEE 
  UNION ALL
SELECT name, 3 FROM EMPLOYEE 
  ORDER BY month, name
;

Try it here: https://onecompiler.com/mysql/3ygcscr33

Note: UNION ALL is required in case names are the same for multiple people.

bloodyKnuckles0
Related