This download is for SQL junkies who need to output SQL tables as HTML tables. This is very useful for generating reports as it allows you within your own T-SQL code to directly transform SQL tables into a format suitable for HTML presentation.
This download is a revision of the original SQL Stored Procedure to Convert SQL Table Into HTML (v1.0) submission I made a while back. This is version 3 just to keep it sane with my own version history (I wrote a v2 but didn't have time to document it on Connect).
In version 2, I really went to town and added the facility to output the table HTML in either horizonal or vertical alignments. First-row and first-column highlighting worked in this version through the addition of an extra style class into the stored procedure arguments. In version 3, I'm simplifying. I'm now returning to a single table style class to define how the table should be formatted in the browser. This is because first-row and first-column formatting can be achieved (as I'll demonstrate) through Cascading Style Sheets (CSS) and the :first-child pseduo class.
How to use CustomTable2HTMLv3
To start using this stored procedure, download the attached zip file CustomTable2HTMLv3.zip, extract from this the .sql file and copy the contents in to SQL Management Studio and execute it.
After execution, you should see the message "Command Completed Successfully" as illustrated above. This means the stored procedure has now been loaded into your database (if you want the procedure to be loaded into a different db, just change the USE line at the top of the script). In the example above, this is being loaded into my eXpress database as I'm currently developing some job reporting queries here.
To show how to use this procedure, let's test it out with the Buffy example which worked so well in the earlier version. Paste the following query into your SQL Server Management query window,
Create table Buffy (
Season int,
episode_number int,
episode_name nvarchar(100)
)
insert into Buffy VALUES (1,1,'Welcome to the Hellmouth')
insert into Buffy VALUES (1,2,'The Harvest')
insert into Buffy VALUES (1,3,'Witch')
insert into Buffy VALUES (1,4,'Teacher''s Pet')
insert into Buffy VALUES (1,5,'Never Kill a Boy on the First Date')
insert into Buffy VALUES (1,6,'The Pack')
insert into Buffy VALUES (1,7,'I, Robot... You, Jane')
insert into Buffy VALUES (1,8,'Nightmares')
insert into Buffy VALUES (1,9,'Out of Mind, Out of Sight')
insert into Buffy VALUES (1,10,'Prophecy Girl')
select * from Buffy
So what we've done is create the Buffy table in our database and then populated it with Season 1 data (if you've added this table into a production database, you might want to cautiously investigate the T-SQL DROP TABLE command when you've finished with this article). The result of the above SQL on execution should be,
To get this table outputted as in both horizonally and vertically formatted HTML tables, the following T-SQL can be used,
DECLARE @HTML1 NVARCHAR(MAX)
DECLARE @HTML2 NVARCHAR(MAX)
EXEC dbo.CustomTable2HTMLv3 'Buffy',@HTML1 OUTPUT,'class="horizontal"',0
EXEC dbo.CustomTable2HTMLv3 'Buffy',@HTML2 OUTPUT,'class="vertical"',1
SELECT @HTML1+@HTML2
Here we can see that the first argument is the table needing to be HTMLified (required), the second is the variable to store the resultant HTML (required), the third is the table style class (optional), and last is the alignment flag (optional). The alignment flag is 0 for horizontally formatted tables (the default) and 1 for vertical alignment. The result of executing the above query is,
<table class="horizonal">
<tr>
<td>Season</td><td>episode_number</td><td>episode_name</td>
</tr>
<tr>
<td>1</td><td>1</td><td>Welcome to the Hellmouth</td>
</tr>
<tr>
<td>1</td><td>2</td><td>The Harvest</td>
</tr>
<tr>
<td>1</td><td>3</td><td>Witch</td>
</tr>
<tr>
<td>1</td><td>4</td><td>Teacher's Pet</td>
</tr>
<tr>
<td>1</td><td>5</td><td>Never Kill a Boy on the First Date</td>
</tr>
<tr>
<td>1</td><td>6</td><td>The Pack</td>
</tr>
<tr>
<td>1</td><td>7</td><td>I, Robot... You, Jane</td>
</tr>
<tr>
<td>1</td><td>8</td><td>Nightmares</td>
</tr>
<tr>
<td>1</td><td>9</td><td>Out of Mind, Out of Sight</td>
</tr>
<tr>
<td>1</td><td>10</td><td>Prophecy Girl</td>
</tr>
</table>
<table class="vertical">
<tr>
<td>Season</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>episode_number</td>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
<td>5</td>
<td>6</td>
<td>7</td>
<td>8</td>
<td>9</td>
<td>10</td>
</tr>
<tr>
<td>episode_name</td>
<td>Welcome to the Hellmouth</td>
<td>The Harvest</td>
<td>Witch</td>
<td>Teacher's Pet</td>
<td>Never Kill a Boy on the First Date</td>
<td>The Pack</td>
<td>I, Robot... You, Jane</td>
<td>Nightmares</td>
<td>Out of Mind, Out of Sight</td>
<td>Prophecy Girl</td>
</tr>
</table>
Using a suitable CSS, this looks as follows when rendered in a browser,
The Cascading Style Sheet
In order to help you style your tables, in the zip download I've also included an HTML file which has an embedded style sheet in the head element. The snazzy bits in here are the first-element pseudo classes,
table.horizontal tr:first-child {
background-color: Gray!important;
font-weight: bold;
color: #fff;
}
table.vertical tr>td:first-child {
background-color: Gray!important;
font-weight: bold;
color: #fff;
}
The first selector, table.horizontal tr:first-child is a selector chain. The first selector matches on the horizontal table class, and the second matches on the pseudo-class first-child, which in turn matches the first child element of the <tr> element i.e. the first row. This is the style which turns the first row's background grey, and formats the text as bold and white.
The second selector, table.vertical tr>td:first-child is a also selector chain. The first selector matches on the vertical table class, and the second matches on the first-child element of each row i.e. the first row using the child selector, >. This is the style which turns the background of the first column grey, formatting the text within as bold and white.
Very powerful stuff I think you'll agree, and many thanks to Ian Senior at OUCS for pointing this out.
How does this stored procedure work?
This procedure works pretty much in the same way as v1, so I recommend you read the first article for my thinking here. The difference between these versions is that I've introduced a small code fork which, depending on the alignment variable, will iterate through the SQL table row-by-row, or column-by-column.
Acknowledgements
Thanks to Ian Senior at Oxford's Computing Services for his keen css knowlege and humor, and to Oxford's many IT Support Staff who are better than Google.
Useful Links
A few links which I found useful when writing this up,