This post is inspired by a blog post I saw here which showed an interesting way to visualise data. The example was not valid syntax for SQL Server so I thought I’d make a version that does work in T-SQL.

Let’s make some sample data. We’re going to use a list of all title characters (heroes with their own independent movies) in the MCU and which films they’ve played a role in.

IF OBJECT_ID('tempdb..#MCUData') IS NOT NULL DROP TABLE #MCUData
CREATE TABLE #MCUData (Hero_Name nvarchar(40), Movie_Name nvarchar(40), Release_Year int)
INSERT INTO #MCUData (Hero_Name, Movie_Name, Release_Year)
VALUES
 ('Iron Man', 'Iron Man', 2008)
,('The Hulk', 'The Incredible Hulk',2008)
,('Iron Man', 'Iron Man 2', 2010)
,('Thor', 'Thor', 2011)
,('Captain America', 'Captain America: The First Avenger', 2011)
,('Iron Man','Marvels The Avengers',2012)
,('The Hulk','Marvels The Avengers',2012)
,('Thor','Marvels The Avengers',2012)
,('Captain America','Marvels The Avengers',2012)
,('Iron Man', 'Iron Man 3', 2013)
,('Thor', 'Thor: The Dark World',2013)
,('Captain America', 'Captain America: The Winter Soldier',2014)
,('Star Lord', 'Guardians of the Galaxy',2014)
,('Iron Man','Avengers: Age of Ultron',2015)
,('The Hulk','Avengers: Age of Ultron',2015)
,('Thor','Avengers: Age of Ultron',2015)
,('Captain America','Avengers: Age of Ultron',2015)
,('Ant Man','Ant Man',2015)
,('Captain America','Captain America: Civil War',2016)
,('Iron Man','Captain America: Civil War',2016)
,('Thor','Captain America: Civil War',2016)
,('The Hulk','Captain America: Civil War',2016)
,('Ant Man','Captain America: Civil War',2016)
,('Spider-Man','Captain America: Civil War',2016)
,('Doctor Strange','Doctor Strange',2016)
,('Star Lord','Guardians of the Galaxy Vol. 2',2017)
,('Spider-Man','Spider-Man: Homecoming',2017)
,('Thor','Thor: Ragnarok',2017)
,('The Hulk','Thor: Ragnarok',2017)

Now, I wonder which character has appeared in the most movies. Let’s work that out. The nifty thing here is that we’re going to see the results in SSMS as a row graph chart, try this;

SELECT 
     mcu.Hero_Name
    ,REPLICATE('|',(COUNT(*)/CONVERT(decimal,m.Movies))*100) Graph
FROM #MCUData mcu
CROSS JOIN (SELECT COUNT(DISTINCT Movie_Name) Movies FROM #MCUData) m
GROUP BY mcu.Hero_Name, m.Movies
ORDER BY mcu.Hero_Name

Take a look at the results pane, you can see that Iron Man and Thor are the most reoccurring Avengers with 35.3% movies each.

Please feel free to point out all of the reasons The list of characters & movies is wrong in the comments section below. I doubt I’d be able to stop you 🙂