Quick Bar Graph in Sql Server

Have you ever needed a bar graph and didn't want to leave Sql Server? Well if you don't have Sql Server 2016 yet I have some code for you. The code below creates a table with the numbers 1-10. We then sample repeatedly from that table and make a histogram of our sample. We should have something approaching a uniform distribution when we are done.

If Object_id('tempdb..#Test','U') is not null 
Drop Table #Test
Create Table #Test ( numbers float )

If Object_id('tempdb..#RandomNumbers','U') is not null 
Drop Table #RandomNumbers
Create Table #RandomNumbers ( numbers float )


-- Create Table with numbers 1 -10
Declare @Counter int
Set @Counter=1
While @Counter <=10
Begin
    Insert Into #Test Select @counter
    Set @Counter=@Counter+1
End

-- Repeated Sample with replacement from table
Declare @Counter2 int
Set @Counter2=1
While @Counter2 <=1000
Begin
    Insert Into #RandomNumbers Select Top 1 Numbers From #test Order By newid()
    Set @Counter2=@Counter2+1
End

-- Make histogram
Select
    numbers,
    replicate('>',count(*)) [Levels]
From
    #RandomNumbers
Group By
    numbers
Order By
    count(*) desc

You should now have a nice little histogram showing you the distribution of your data. Another useful thing is to approach it like a pareto chart and order the data descending.

Select
    numbers,
    replicate('>',count(*))[Levels]
From
    #RandomNumbers
Group By
    numbers
Order By
    count(*) desc