by 
It might be our fault for having our new interns look at our data collection but not everyone at SuperHero Inc. is as data-savvy as we would like.
We’ve gotten reports that they’ve simply taken some old paper marking sheets and turned them into gathered data.
Our analysts are less than enthusiastic about the results and have asked us to clean up the data a little.
The superheroes are currently logged on a single row and a ++, + or – has been marked in the column for every superpower that they have.
The analysts are having issues counting the values and easily displaying the values and they’ve asked if we could create a new row for every hero’s power.
What the interns created looks like the following :
As you can see , every hero has 2 powers, noted as ‘++’ for the main power and + for a secondary power.
What the analysts are looking for is the following result :
The assignment for this week :
- Transform the data in such a way that every hero gets 1 row
- Put the ++ powers in the main_power column
- Put the + powers in the secondary_power column
Starting Data
create or replace table hero_powers (
hero_name VARCHAR(50),
flight VARCHAR(50),
laser_eyes VARCHAR(50),
invisibility VARCHAR(50),
invincibility VARCHAR(50),
psychic VARCHAR(50),
magic VARCHAR(50),
super_speed VARCHAR(50),
super_strength VARCHAR(50)
);
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Impossible Guard', '++', '-', '-', '-', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Clever Daggers', '-', '+', '-', '-', '-', '-', '-', '++');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Quick Jackal', '+', '-', '++', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Steel Spy', '-', '++', '-', '-', '+', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Agent Thundering Sage', '++', '+', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Mister Unarmed Genius', '-', '-', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Doctor Galactic Spectacle', '-', '-', '-', '++', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Master Rapid Illusionist', '-', '-', '-', '-', '++', '-', '+', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Galactic Gargoyle', '+', '-', '-', '-', '-', '-', '++', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Alley Cat', '-', '++', '-', '-', '-', '-', '-', '+');Remember, if you want to participate:
Another fun challenge and a great way to start the day!
this was fun 🙂
Nice refresher for the (un)pivot syntax
Getting started with an easy one 🙂
Nice challenge to remember how easy it is to pivot in sql instead of in BI tools 🙂
My solution
My solution
For me, pivoting is never easy 🙂
Nevertheless, this is my solution..
Not the most intuitive on the order for pivoting, hopefully more practice will make me more familiar with it.
Nice one, this was!