I want to populate my temporary table (tmp) with the result of the SELECT statement based on the species of the pet from the pets table. But I am getting NULL for all columns This is my CREATE TABLE with the queries I have written so far

create table pets ( name varchar(255), species varchar(255) );

insert into pets (name, species) values ('Barker', 'weasel'), ('Chevy', 'chinchilla'), ('Fay', 'chinchilla'), ('Grit', 'cat'), ('Hex', 'dog'), ('Lunar', 'dog'), ('Mojo', 'chinchilla'), ('Peter', 'weasel'), ('Shogun', 'cat'), ('Sierra', 'cat');

DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
cats VARCHAR(255), 
dogs VARCHAR(255), 
weasels VARCHAR(255), 
chinchillas VARCHAR(255));

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
VALUES
(
 (SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'cat')),
 (SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'dog')),
 (SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'weasel')),
 (SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'chinchilla'))
);


SELECT * FROM tmp;

I want to achieve the expected output in this image

CodePudding user response：

You can achieve it like this (result here)

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT 
 case species when 'cat' then name else '' end,
 case species when 'dog' then name else '' end,
 case species when 'weasel' then name else '' end,
 case species when 'chinchilla' then name else '' end
from pets;

CodePudding user response：

It's a bit complicated but it's possible like this:

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT MAX(CASE WHEN species='cat' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'cats',
       MAX(CASE WHEN species='dog' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'dogs',
       MAX(CASE WHEN species='weasel' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'weasel',
       MAX(CASE WHEN species='chinchilla' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'chinchillas' 
  FROM
  (SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species) p 
 JOIN 
  (SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
    ON seq <= cnt
GROUP BY seq
ORDER BY seq;

The base query component is:

SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species

That will return the following result:

Make that as a subquery then add a query to generate a custom number sequences to JOIN with the base query:

(SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
/*with ON like this*/
  ON seq <= cnt

The main highlight here is probably the MAX(CASE .. expression in SELECT. We're doing SUBSTRING_INDEX() twice and make use of the custom generated sequence numbers to obtain which part of the group concatenated value of sname to get.

Demo fiddle