Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - Quick and Dirty Results Graph

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        25 Aug 2009

Description

This is a technique to produce a quick character-graph representation of results.
 
There are no dynamic parameters to allow the graphs to be automatically sized to the results, although I'm sure this could be wrapped in a stored procedure. 
 

Code

The technique:

SELECT OrderDate
    
, COUNT(*) AS 'Orders'
    
, CAST(REPLICATE('=', COUNT(*)) AS VARCHAR(30))AS 'OrderGraph'
    
, SUM(TotalDue) AS 'Revenue'
    
, CAST(REPLICATE('$', SUM(TotalDue)/1000)  AS VARCHAR(50)) AS 'RevenueGraph'
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate Between '2003-07-15' And '2003-07-31'
GROUP BY OrderDate
ORDER BY OrderDate;


 The results:

 OrderDate   Orders OrderGraph                     Revenue    RevenueGraph
----------- ------ ------------------------------ ---------- --------------------------------------------------
15 Jul 2003 19     ===================           
34025.24   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
16 Jul 2003 14    
==============                
26687.65   $$$$$$$$$$$$$$$$$$$$$$$$$$$
17 Jul 2003 16    
================              
32411.93   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
18 Jul 2003 9      
=========                      
18634.91   $$$$$$$$$$$$$$$$$$$
19 Jul 2003 13    
=============                  
19603.23   $$$$$$$$$$$$$$$$$$$$
21 Jul 2003 9      =========                      11781.62   $$$$$$$$$$$$
22 Jul 2003 17    
=================              
32322.50   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
23 Jul 2003 15    
===============                
30906.44   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
25 Jul 2003 15     ===============                27058.10   $$$$$$$$$$$$$$$$$$$$$$$$$$$
26 Jul 2003 18    
==================            
41076.49   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
27 Jul 2003 15    
===============                
22169.88   $$$$$$$$$$$$$$$$$$$$$$
28 Jul 2003 16    
================              
23945.80   $$$$$$$$$$$$$$$$$$$$$$$$
30 Jul 2003 12     ============                   23476.44   $$$$$$$$$$$$$$$$$$$$$$$
31 Jul 2003 18    
==================             36266.76   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
  

 

 


 
ċ
Graph with Text.sql
(0k)
Andy Hughes,
Jun 8, 2012, 7:52 AM
Comments