Creating an HTML table string in SQL from a result with gap values

Question

I'm using a mailing template engine that can't accept anything other than SQL output (due to this already being a subquery), so I'm trying to output an HTML table directly as a string.

I need to create a SQL script that through some form of black magic creates a table out of a set of shows with the days as columns, and the show times as table cells. For example:

FR      SA      SU      MO      TU      WE      TH      FR
16:00   11:10   11:10           16:00   12:50
        13:40   13:40                   15:10
        16:00   16:00

I've got a query that gets all the required data and its column and row numbers, but no idea how to turn that into a string value that looks like this for each row:

<tr><td>16:00</td><td>11:10</td><td>11:10</td><td></td><td>16:00</td><td>12:50</td><td></td><td></td></tr>

Here's the query (and sample data) I'm using now:

declare @Shows table(id int, start datetime);
insert into @Shows(id, start) values (801, '2013-12-13 16:00');
insert into @Shows(id, start) values (325, '2013-12-14 11:10');
insert into @Shows(id, start) values (662, '2013-12-14 13:40');
insert into @Shows(id, start) values (771, '2013-12-14 16:00');
insert into @Shows(id, start) values (772, '2013-12-15 11:10');
insert into @Shows(id, start) values (441, '2013-12-15 13:40');
insert into @Shows(id, start) values (775, '2013-12-15 16:00');
insert into @Shows(id, start) values (138, '2013-12-17 16:00');
insert into @Shows(id, start) values (238, '2013-12-18 12:50');
insert into @Shows(id, start) values (947, '2013-12-18 15:10');


declare @DayWrapHour int=3;
declare @FromDate datetime='2013-12-13 15:00';
declare @ToDate datetime = dateadd(day, 8, CONVERT(date, @FromDate));
set @ToDate = DATEADD(hour, @DayWrapHour, @ToDate); --8 days from now, at 3 am

select *, ROW_NUMBER() over (partition by columnindex order by columnindex) as rownumber
from (
    select
        s.Id,
        CONVERT(char(5), CONVERT(time, s.start)) as StartTime,
        DATEDIFF(DAY,
            DATEADD(HOUR, @DayWrapHour, CONVERT(datetime, CONVERT(date, @FromDate))), --@FromDate, at 3 am
            s.start
        ) as columnindex
    from @Shows s
    where s.start between @FromDate and @ToDate
)as sub
order by rownumber, sub.columnindex

I thought that was quite clever already, but I'm stumped on how to do a sort of foreach on this without using non-SQL code, and on how to deal with the data gaps if I do.

Output of that query:

Id  StartTime   columnindex rownumber
801 16:00       0           1
325 11:10       1           1
772 11:10       2           1
138 16:00       4           1
238 12:50       5           1
662 13:40       1           2
441 13:40       2           2
947 15:10       5           2
771 16:00       1           3
775 16:00       2           3

Note that the column amount is fixed, but the row amount is not.

Why are you forcing SQL Server to create HTML for you? Your client application code is going to be much better at the type of nested looping you need than Transact-SQL will ever be. — Aaron Bertrand, Dec 13 '13 at 15:57
I agree, and I wish I had that luxury. This is for a system that can actually generate HTML per row, but since this is a subquery to one of those rows, I need to output HTML directly. I am aware that I am creating a monster here (if it works). Changing the system I'm using would be nice, but not feasible in the short term I need this result. If this doesn't work, I guess I'll make a console app in .NET to generate the HTML, then paste that into the template generator, which is hardly ideal either. — Twon-ha, Dec 13 '13 at 16:02
Check out http://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml — John Gibb, Dec 13 '13 at 16:20
@JohnGibb thanks! That does help, but I'm not sure how to convert the result you see at the query output to the same table structure I want in the end. — Twon-ha, Dec 13 '13 at 16:22

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

You can use something like this to pivot the columns:

select 
    rownumber
,   col0 = max(case when columnindex = 0 then StartTime end)
,   col1 = max(case when columnindex = 1 then StartTime end)
,   col2 = max(case when columnindex = 2 then StartTime end)
from (
   -- your previous query with the rownumber and columnindex
) x
group by rownumber

And then for xml to generate the html, like they do in this answer: Create HTML Table with SQL FOR XML

edited May 23 '17 at 12:11

Community

1
1

answered Dec 13 '13 at 16:27

John Gibb

10,603
2
37
48

I had to put the result in a bunch of subqueries, but this was the hint I needed to get the result in the question. Thanks! I'll need to figure out something to make those times into valid hyperlinks, but this answers the question as it was posed. – Twon-ha Dec 13 '13 at 17:31

Creating an HTML table string in SQL from a result with gap values

1 Answers1