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:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge
13 responses to “Week 21 – Basic”
-
Another fun challenge and a great way to start the day!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
this was fun 🙂
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_21.sql
-
Nice refresher for the (un)pivot syntax
- Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w21.sql
-
Getting started with an easy one 🙂
- Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/999558daac287e6ca325ecb5b9dbec1fcb26de08/Week%2021/week_21_jlaird.sql
-
Nice challenge to remember how easy it is to pivot in sql instead of in BI tools 🙂
- Solution URL – https://github.com/DownToEarthDataTips/FrostyFridays/blob/main/Week%2019%20-%20Pivoting
-
My solution
- Solution URL – https://github.com/isarenz/FrostyFridayChallenges/blob/main/FrostyFriday_Challenge_Week21.sql
-
My solution
- Solution URL – https://github.com/CSimonini/Frosty_Friday/blob/main/W21_Solution.sql
-
For me, pivoting is never easy 🙂
Nevertheless, this is my solution..- Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch_21_pivot.sql
-
Not the most intuitive on the order for pivoting, hopefully more practice will make me more familiar with it.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF21_LZ.sql
-
Nice one, this was!
- Solution URL – https://github.com/arjansnowflake/Frosty_Friday/tree/main/Week_21
-
went down the CASE statement route
- Solution URL – https://github.com/NMangera/frosty_friday/blob/main/wk%2021%20-%20basic/pivot
-
This is my version of the solution for this task. I hope you find it helpful! ^^
- Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2021%20%E2%80%93%20Basic
-
I solved it using three methods:
1. unpivot + pivot
2. unpivot + dynamic pivot
3. dynamic unpivot( using object_construnct) + dynamic pivotSince it’s tough when there are thousands of column names, I considered whether there might be a way to use pivot and unpivot without having to list all the column names.
- Solution URL – https://github.com/gakut12/Frosty-Friday/blob/main/week21_basic_pivoting/week21.sql
Leave a Reply
You must be logged in to post a comment.