Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 8, 2000

Create Bar Charts

By Alan Enderby

Create bar charts, even includes X and Y labels!

create proc Hbar
@cmd varchar(255) , /* TSQL SELECT statement 1st column
Character, 2nd column numeric */
@barsize int=100 , /* Maximum size of bar */
@data_label bit=1, /* Label each bar with value 1=yes */
@x_label varchar(25)=null,
@y_label varchar(25)=null,
@title varchar(255)=null, /* Title for chart */
@barchar char(1)='*' /* Character to use for bar */
as

SET NOCOUNT ON

-- Get x & y into table #t1

CREATE TABLE #t1 (x varchar(25),y real)
INSERT INTO #t1 exec (@cmd)

-- Format bars

DECLARE @barsize2 varchar(3) Select
@barsize2=convert(varchar(8),@barsize)
DECLARE @max real,@i varchar(12)
SELECT @max=max(y) from #t1
SELECT @i = str(@barsize/@max,6,6)

CREATE TABLE #t2 (x varchar(25),bar varchar(255),y real)
INSERT INTO #t2 EXEC ("SELECT right(replicate(' ',25)+x,25),
Bar=convert(char("+ @barsize2 +"),
isnull(replicate('"+@barchar+"',convert(int,y*" + @i +")),'')),y
from #t1")

-- Label bars if required

IF @data_label =1
UPDATE #t2 SET bar=bar+' ('+convert(varchar(14),y)+ ')'

-- Print title

DECLARE @line varchar(255) ,@c int
SELECT @c=((@barsize+50)-datalength(@title))/2
SELECT @line = REPLICATE(' ',@c) + @TITLE
PRINT @line
SELECT @line = REPLICATE(' ',@c) + replicate
('=',datalength(@title))
PRINT @line
PRINT ' '
SELECT @line=right(REPLICATE(' ',25)+@x_label,25)
PRINT @line
PRINT ' '

-- print chart

DECLARE hb_cursor INSENSITIVE CURSOR
FOR SELECT x,bar,y
FROM #t2

DECLARE @x varchar(25), @bar varchar(255) ,@y real

OPEN hb_cursor
WHILE 1=1
BEGIN
FETCH NEXT FROM hb_cursor INTO @x ,@bar ,@y
IF @@FETCH_STATUS <> 0
BREAK
EXEC (' PRINT "' + @x + ' | ' + @bar +'"')
END
DEALLOCATE hb_cursor

-- Print y axis

DECLARE @Scale varchar(255) , @y_axis varchar(255)
SELECT @scale = ' +'
SELECT @y_axis = ' 0'

DECLARE @ten int SELECT @ten=@max/(@barsize/10)

DECLARE @ten2 int SELECT @ten2 = @ten

WHILE @ten2 < @max+@ten
BEGIN
SELECT @scale = @scale + '----+----+'
SELECT @y_axis = @y_axis + right ('
'+convert(varchar(5),@ten2),10)
SELECT @ten2=@ten2 + @ten
END
PRINT @scale
PRINT @y_axis
PRINT ' '
SELECT @line = REPLICATE(' ',@c) + @y_label
PRINT @line
PRINT ' '
go

/* TEST DATA */

exec Hbar 'select convert(varchar(5),spid),cpu from
master..sysprocesses where cpu<>0',
@barsize=100,
@data_label=0,
@title='Processes by CPU',
@barchar='#',
@x_label='SPID',
@y_label='CPU'

set nocount on
declare @cmd varchar(255)
select @cmd="
select o.name, size=sum(used * 2)
from sysindexes i, sysobjects o ,syscolumns c
where i.id = o.id
and i.indid in (0, 1, 255)
and c.id = o.id
and o.name not like 'sys%'

GROUP BY o.name,o.id "

exec Hbar @cmd,@barsize=100,@data_label=1,@title='Tables by Size'



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM