Dynamic variables in SQL server for HTML

Question

Dynamic variables in SQL server for HTML

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99135 More actions · Answer 1

Ok, I don't like this solution, in fact, I really dislike it, but here's what I have... it works for the data you supplied, but you're likely going to need to do tweaks.

--DECLARE HTML Variable
DECLARE @TableHTML nvarchar(max);
--Start Table markup
SET @TableHTML = N'<table border="1">' + NCHAR(10);
--DECLARE a dynamic SQL variable
DECLARE @SQL varchar(max);
/*
These are your RowHeadings. This needs to have the following:
1. The sequence you want the heading to appear
2. The name of the Row Heading.
3. The name of the column it represents in the table. These SHOULD be quoted, i jsut have not bohtered.
4. because date(time)s can look crap, a format for your dates. Leave as NULL for non-date(time)s values.
*/
WITH RowHeadings AS (
SELECT *
FROM (VALUES (1,'Bill Run','BillRun',NULL),
     (2,'Bill Date','bill_date',106),
      (3,'Invoice Count','invoice_count',NULL),
      (4,'Billed Days','BilledDays',NULL),
      (5,'Aged Billed Days','AgedBilledDays',NULL),
      (6,'Min Invoice Due','MinInvoiceDue',NULL),
      (7,'Max Invoice Due','MaxInvoiceDue',NULL),
      (8,'Total Current Invoice Changes (Inv VAT)','TotalCurrentInvoiceChargesIncVat',NULL),
      (9,'Total Due (Inc VAT)','TotalDueIncVat',NULL),
      (10,'VDD Payment Date','VDDPaymentDate',106)) RH (RowNum, Heading, ColumName, DateConv))
--Create the Dynamic SQL
SELECT @SQL = REPLACE(REPLACE(REPLACE(STUFF(
(SELECT ' + ''<tr>' + CHAR(10) + '<td>' + RH.Heading + '</td>'' +' + CHAR(10) +
   '(SELECT CHAR(10) + ''<td>'' + CONVERT(varchar(50),' + RH.ColumName + CASE WHEN RH.DateConv IS NOT NULL THEN ',' + CONVERT(varchar(3), RH.DateConv) ELSE '' END + ') + ''</td>''
     FROM #test_data
     ORDER BY BillRun ASC
     FOR XML PATH('''')) + CHAR(10) + ''</tr>'' + CHAR(10)'
 FROM RowHeadings RH
 ORDER BY RH.RowNum
 --Lots of replacement for escape characters!
 FOR XML PATH ('')),1,3,''),'&gt;','>'),'&lt;','<'),'',CHAR(10));
 
 --Add Select at the start
SET @SQL = 'SELECT ' + @SQL;
--We need to put the value into a table, so we can assign it to a variable
CREATE TABLE #TempHTML (HTML nvarchar(max));
--insert into the table
INSERT INTO #TempHTML
EXEC (@SQL);
--Put it all together
SET @TableHTML = @TableHTML + REPLACE(REPLACE(REPLACE((SELECT HTML FROM #TempHTML),'&gt;','>'),'&lt;','<'),'',CHAR(10)) + CHAR(10) + '</table>';
--And Print!
PRINT @TableHTML;
--Drop the temporary Table
DROP TABLE #TempHTML;
GO

This produces the following HTML:

 <table border="1">
   <tr>
   <td>Bill Run</td>
   <td>369873</td>
   <td>369874</td>
   <td>369875</td>
   <td>369876</td>
   <td>369877</td>
   <td>369878</td>
   <td>369879</td>
   </tr>
   <tr>
   <td>Bill Date</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   </tr>
   <tr>
   <td>Invoice Count</td>
   <td>6</td>
   <td>6</td>
   <td>3</td>
   <td>5</td>
   <td>9</td>
   <td>11</td>
   <td>1</td>
   </tr>
   <tr>
   <td>Billed Days</td>
   <td>3047</td>
   <td>2181</td>
   <td>2931</td>
   <td>6089</td>
   <td>5126</td>
   <td>3485</td>
   <td>855</td>
   </tr>
   <tr>
   <td>Aged Billed Days</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   </tr>
   <tr>
   <td>Min Invoice Due</td>
   <td>-261.24</td>
   <td>-283.24</td>
   <td>-184.63</td>
   <td>-149.89</td>
   <td>-384.76</td>
   <td>-577.76</td>
   <td>605.27</td>
   </tr>
   <tr>
   <td>Max Invoice Due</td>
   <td>0.03</td>
   <td>0.03</td>
   <td>4270.84</td>
   <td>2218.31</td>
   <td>2403.05</td>
   <td>3108.78</td>
   <td>605.27</td>
   </tr>
   <tr>
   <td>Total Current Invoice Changes (Inv VAT)</td>
   <td>1901.24</td>
   <td>1623.24</td>
   <td>4379.66</td>
   <td>3059.52</td>
   <td>3477.56</td>
   <td>7870.38</td>
   <td>159.95</td>
   </tr>
   <tr>
   <td>Total Due (Inc VAT)</td>
   <td>-417.61</td>
   <td>-834.61</td>
   <td>6438</td>
   <td>2039.23</td>
   <td>3079.47</td>
   <td>4432.59</td>
   <td>605.27</td>
   </tr>
   <tr>
   <td>VDD Payment Date</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   </tr>
   
   </table>

Which makes a table looking like this:

If there's any parts you don't under stand, please ask, however, it's likely that I'm only going to be able to supply a limited amount of trouble shooting. You're going to need to do that yourself.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 2

Thom A - Tuesday, November 7, 2017 3:35 AM
Ok, I don't like this solution, in fact, I really dislike it, but here's what I have... it works for the data you supplied, but you'll likely going to need to do tweaks.
--DECLARE HTML Variable
DECLARE @TableHTML nvarchar(max);
--Start Table markup
SET @TableHTML = N'<table border="1">' + NCHAR(10);
--DECLARE a dynamic SQL variable
DECLARE @SQL varchar(max);
/*
These are your RowHeadings. This needs to have the following:
1. The sequence you want the heading to appear
2. The name of the Row Heading.
3. The name of the column it represents in the table. These SHOULD be quoted, i jsut have not bohtered.
4. because date(time)s can look crap, a format for your dates. Leave as NULL for non-date(time)s values.
*/
WITH RowHeadings AS (
SELECT *
FROM (VALUES (1,'Bill Run','BillRun',NULL),
     (2,'Bill Date','bill_date',106),
     (3,'Invoice Count','invoice_count',NULL),
     (4,'Billed Days','BilledDays',NULL),
     (5,'Aged Billed Days','AgedBilledDays',NULL),
     (6,'Min Invoice Due','MinInvoiceDue',NULL),
     (7,'Max Invoice Due','MaxInvoiceDue',NULL),
     (8,'Total Current Invoice Changes (Inv VAT)','TotalCurrentInvoiceChargesIncVat',NULL),
     (9,'Total Due (Inc VAT)','TotalDueIncVat',NULL),
     (10,'VDD Payment Date','VDDPaymentDate',106)) RH (RowNum, Heading, ColumName, DateConv))
--Create the Dynamic SQL
SELECT @SQL = REPLACE(REPLACE(REPLACE(STUFF(
(SELECT ' + ''<tr>' + CHAR(10) + '<td>' + RH.Heading + '</td>'' +' + CHAR(10) +
   '(SELECT CHAR(10) + ''<td>'' + CONVERT(varchar(50),' + RH.ColumName + CASE WHEN RH.DateConv IS NOT NULL THEN ',' + CONVERT(varchar(3), RH.DateConv) ELSE '' END + ') + ''</td>''
    FROM #test_data
    ORDER BY BillRun ASC
    FOR XML PATH('''')) + CHAR(10) + ''<tr>'' + CHAR(10)'
FROM RowHeadings RH
ORDER BY RH.RowNum
--Lots of replacement for escape characters!
FOR XML PATH ('')),1,3,''),'>','>'),'<','<'),' ',CHAR(10));

--Add Select at the start
SET @SQL = 'SELECT ' + @SQL;
--We need to put the value into a table, so we can assign it to a variable
CREATE TABLE #TempHTML (HTML nvarchar(max));
--insert into the table
INSERT INTO #TempHTML
EXEC (@SQL);
--Put it all together
SET @TableHTML = @TableHTML + REPLACE(REPLACE(REPLACE((SELECT HTML FROM #TempHTML),'>','>'),'<','<'),' ',CHAR(10)) + CHAR(10) + '</table>';
--And Print!
PRINT @TableHTML;
--Drop the temporary Table
DROP TABLE #TempHTML;
GO
This produces the following HTML:
<table border="1">
   <tr>
   <td>Bill Run</td>
   <td>369873</td>
   <td>369874</td>
   <td>369875</td>
   <td>369876</td>
   <td>369877</td>
   <td>369878</td>
   <td>369879</td>
   <tr>
   <tr>
   <td>Bill Date</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <tr>
   <tr>
   <td>Invoice Count</td>
   <td>6</td>
   <td>6</td>
   <td>3</td>
   <td>5</td>
   <td>9</td>
   <td>11</td>
   <td>1</td>
   <tr>
   <tr>
   <td>Billed Days</td>
   <td>3047</td>
   <td>2181</td>
   <td>2931</td>
   <td>6089</td>
   <td>5126</td>
   <td>3485</td>
   <td>855</td>
   <tr>
   <tr>
   <td>Aged Billed Days</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <tr>
   <tr>
   <td>Min Invoice Due</td>
   <td>-261.24</td>
   <td>-283.24</td>
   <td>-184.63</td>
   <td>-149.89</td>
   <td>-384.76</td>
   <td>-577.76</td>
   <td>605.27</td>
   <tr>
   <tr>
   <td>Max Invoice Due</td>
   <td>0.03</td>
   <td>0.03</td>
   <td>4270.84</td>
   <td>2218.31</td>
   <td>2403.05</td>
   <td>3108.78</td>
   <td>605.27</td>
   <tr>
   <tr>
   <td>Total Current Invoice Changes (Inv VAT)</td>
   <td>1901.24</td>
   <td>1623.24</td>
   <td>4379.66</td>
   <td>3059.52</td>
   <td>3477.56</td>
   <td>7870.38</td>
   <td>159.95</td>
   <tr>
   <tr>
   <td>Total Due (Inc VAT)</td>
   <td>-417.61</td>
   <td>-834.61</td>
   <td>6438</td>
   <td>2039.23</td>
   <td>3079.47</td>
   <td>4432.59</td>
   <td>605.27</td>
   <tr>
   <tr>
   <td>VDD Payment Date</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <td>07 Nov 2017</td>
   <tr>

   </table>
Which makes a table looking like this:

If there's any parts you don't under stand, please ask, however, it's likely that I'm only going to be able to supply a limited amount of trouble shooting. You're going to need to do that yourself.

Thanks Thom, will give it a go.

Thom A SSC Guru Points: 99135 More actions · Answer 3

70712 - Tuesday, November 7, 2017 3:41 AM
Thanks Thom, will give it a go.

Noticed a slight error (I hadn't closed my <tr>'s). I've edited my post.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 4

Why do you need to know how many columns though? If you just add the tags for the row at the beginning and end of your SELECT, and add tags for each cell around each field in the SELECT, then SQL will just spit out a formatted HTML table?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Thom A SSC Guru Points: 99135 More actions · Answer 5

jonathan.crawford - Tuesday, November 7, 2017 4:59 AM
Why do you need to know how many columns though? If you just add the tags for the row at the beginning and end of your SELECT, and add tags for each cell around each field in the SELECT, then SQL will just spit out a formatted HTML table?

It's not really the number of columns, but the number of rows in the table, as the OP wants their data pivoted.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 6

jonathan.crawford - Tuesday, November 7, 2017 4:59 AM
Why do you need to know how many columns though? If you just add the tags for the row at the beginning and end of your SELECT, and add tags for each cell around each field in the SELECT, then SQL will just spit out a formatted HTML table?

When selecting from the pivoted table, if there are four columns with data columns then I need to say that no?

e.g header, 1, 2, 3, 4

70712 Old Hand Points: 347 More actions · Answer 7

Thom A - Tuesday, November 7, 2017 3:44 AM
70712 - Tuesday, November 7, 2017 3:41 AM
Thanks Thom, will give it a go.
Noticed a slight error (I hadn't closed my <tr>'s). I've edited my post.

Thom, this looks to be working perfectly, Thank you so very much for your help.

Thom A SSC Guru Points: 99135 More actions · Answer 8

70712 - Tuesday, November 7, 2017 8:16 AM
Thom, this looks to be working perfectly, Thank you so very much for your help.

Glad to hear it's working. Does it make sense to you?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 9

Thom A - Tuesday, November 7, 2017 8:22 AM
70712 - Tuesday, November 7, 2017 8:16 AM
Thom, this looks to be working perfectly, Thank you so very much for your help.
Glad to hear it's working. Does it make sense to you?

In parts, I'm currently going through trying to understand fully.

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 10

70712 - Tuesday, November 7, 2017 5:04 AM
jonathan.crawford - Tuesday, November 7, 2017 4:59 AM
Why do you need to know how many columns though? If you just add the tags for the row at the beginning and end of your SELECT, and add tags for each cell around each field in the SELECT, then SQL will just spit out a formatted HTML table?
When selecting from the pivoted table, if there are four columns with data columns then I need to say that no?
e.g header, 1, 2, 3, 4

not if you make it dynamic, but Thom's got you covered, I didn't see his post prior to mine before I responded.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

70712 Old Hand Points: 347 More actions · Answer 11

jonathan.crawford - Wednesday, November 8, 2017 10:14 AM
70712 - Tuesday, November 7, 2017 5:04 AM
jonathan.crawford - Tuesday, November 7, 2017 4:59 AM
Why do you need to know how many columns though? If you just add the tags for the row at the beginning and end of your SELECT, and add tags for each cell around each field in the SELECT, then SQL will just spit out a formatted HTML table?
When selecting from the pivoted table, if there are four columns with data columns then I need to say that no?
e.g header, 1, 2, 3, 4
not if you make it dynamic, but Thom's got you covered, I didn't see his post prior to mine before I responded.

Ha yes, everything is working fine thanks.