Return Select Statement as formatted HTML

Question

I have a data set that looks something like this:

   Gender | Age | Name
    Male  | 30  | Bill
  Female  | 27  | Jenny
  Female  | 27  | Debby 
   Male   | 44  | Frank

And I'm trying to display this as specially formatted HTML code:

    <ul>
      <li>Male
        <ul>
          <li>30
            <ul>
              <li>Bill</li>
            </ul>
          </li>
          <li>44
            <ul>
              <li>Frank</li>
            </ul>
          </li>
        </ul>  
      </li>
    </ul>

    <ul>
      <li>Female
        <ul>
          <li>27
            <ul>
              <li>Jenny</li>
              <li>Debby</li>
            </ul>
          </li>
        </ul>  
      </li>
    </ul>

I tried using FOR XML but that didn't give the results I was looking for. It didn't remove the multiple Gender and Age fields returned. As you can see in this HTML it is compounding it all and only giving duplicates at the end node.

How would something like this be achieved in SQL Server?

Hello again Aaron, Lets just say it doesn't go outside of the SQL layer — Control Freak, Apr 23 '12 at 23:51
Presentation belongs on the client, not on the DB. If you just enjoy inflicting yourself pain, there are other ways not involving SQL. — Remus Rusanu, Apr 24 '12 at 00:24
Maybe consider a CLR function? There has to be a better way to do this in C#/VB .NET. — JeffO, Apr 24 '12 at 03:10

score 5 · Accepted Answer · edited May 23 '17 at 11:49

5

Here is a really ugly way formulating the HTML manually. There is a good reason this doesn't belong in SQL Server. I'm sure some XML guru will come along and embarrass me with a much more straightforward method (I played with Simon Sabin's solution but couldn't translate it to your requirement), but for now:

DECLARE @x TABLE(Gender VARCHAR(6), Age INT, Name VARCHAR(32));

INSERT @x VALUES  ('Male',   30, 'Bill'),  ('Female', 27, 'Jenny'),
                  ('Female', 27, 'Debby'), ('Male',   44, 'Frank');

DECLARE @html NVARCHAR(MAX) = N'';

;WITH x AS ( SELECT x.Age, x.Gender, x.Name,
    dr = DENSE_RANK() OVER (PARTITION BY x.Gender ORDER BY x.Age),
    gn = ROW_NUMBER() OVER (PARTITION BY x.Gender ORDER BY x.Age),
    rn = ROW_NUMBER() OVER (ORDER BY x.Gender DESC, x.Age)
  FROM @x AS x ) SELECT @html +=
    CHAR(13) + CHAR(10) + CASE WHEN c1.gn = 1 THEN 
        CASE WHEN c1.rn > 1 THEN '</li></ul></li></ul>' ELSE '' END + '<ul><li>' 
        + c1.Gender ELSE '' END + CHAR(13) + CHAR(10) + CHAR(9) 
        + CASE WHEN c1.gn = 1 OR c1.Age <> c3.Age THEN 
        CASE WHEN c1.gn > 1 THEN '</li>' ELSE '<ul>' END + '<li>' 
        + CONVERT(VARCHAR(32), c1.Age) ELSE '' END + CHAR(13) + CHAR(10) + CHAR(9) 
        + CHAR(9) + CASE WHEN (c1.gn = 1 OR c1.Age <> c3.Age) THEN '<ul>' ELSE '' END 
        + '<li>' + c1.Name + '</li>' + CASE WHEN c1.Age <> c2.Age OR c1.dr <> c2.dr 
        THEN '</ul>' ELSE '' END
FROM x AS c1 
LEFT OUTER JOIN x AS c2
ON c1.rn = c2.rn - 1
LEFT OUTER JOIN x AS c3
ON c1.rn = c3.rn + 1
ORDER BY c1.Gender DESC, c1.Age;

SELECT @html += '</ul></li></ul></li></ul>';

PRINT @html; -- note you will need to deal with this 
             -- in another way if the string is large

Result - not exactly what you asked for in terms of white space, but identical HTML rendering:

<ul><li>Male
    <ul><li>30
        <ul><li>Bill</li></ul>

    </li><li>44
        <ul><li>Frank</li></ul>
</li></ul></li></ul><ul><li>Female
    <ul><li>27
        <ul><li>Jenny</li>


        <li>Debby</li></ul></li></ul></li></ul>

EDIT For a much cleaner solution, as well as a lot of drama and a good demonstration of why @ZeeTee is the most annoying user on StackOverflow, see Mikael's solution to the follow-up question:

Return Select Statement as formatted HTML (SQL 2005)

edited May 23 '17 at 11:49

Community

1
1

answered Apr 24 '12 at 01:04

Aaron Bertrand

272,866
37
466
490

Actually, it's not proper HTML, there are 2 tags that aren't closed, see your HTML code. – Control Freak Apr 24 '12 at 02:15
+1 This would be a good T-SQL challenge. It would be very difficult to do using "for xml" because the closing of tags is conditionally based upon the previous row. Aaron has used the ranking functions and left joins to figure out whether to close the tag or keep it open. I'll be curious to see if anyone can solve using "for xml". – brian Apr 24 '12 at 03:15
It works perfectly in '08, thank you. But just for the sake of asking.. What would be different in SQL '05 besides the default value set on `DECLARE @html` and the `+=` operators? – Control Freak Apr 24 '12 at 14:52
No difference that I'm aware of. Did you try it? – Aaron Bertrand Apr 24 '12 at 14:54
Yes, it works in '08, but in '05 it just says `x number of rows completed` in '08 it says the same thing, but it actually prints a value for `@html` (the html code). In '05 its not printing a value for `@html` – Control Freak Apr 24 '12 at 14:56
hmm i tried adding a `where is not null` while building the CTE, still same result.. Should i ask this in a new question? – Control Freak Apr 24 '12 at 15:00
by the way, none of the values are null in the dataset (in the CTE) and even in your example none are null, your example dataset is giving the same result also. – Control Freak Apr 24 '12 at 15:02
Yup, even in your example dataset, gives the same result in 05 – Control Freak Apr 24 '12 at 15:04
What do you mean about I can't use values in @table, it seemed to work after changing the INSERT statement a bit.. Yes, I know about the wasted time.. I've posted a new question here: http://stackoverflow.com/questions/10300764/return-select-statement-as-formatted-html-sql-2005 – Control Freak Apr 24 '12 at 15:11
I mean `INSERT @x VALUES (),(),...` is not valid in SQL Server 2005. Anyway I've answered your other question with updated syntax for SQL Server 2005. If that doesn't produce results for you, then there is something you're not telling us. – Aaron Bertrand Apr 24 '12 at 15:14

score 0 · Answer 2 · edited Jan 18 '21 at 12:32

Note: I have replaced for $g in ("Male", "Female") with for $g in distinct-values(//root /row/@Gender) .

Note: 2 I have removed duplicate ages for every gender (demo here).

Also, this could be done using XQueries:

DECLARE @x XML = 
(
    SELECT  *
    FROM    @Test t
    FOR XML RAW, ROOT
);
SELECT @x AS [Source];
SELECT @x.query('
    for $g in distinct-values(//root/row/@Gender) (: or for $g in ("Male", "Female") :)
    return
    <ul>
        <li>
            {data($g)}
            <ul>
            {
                for $a in distinct-values(//root/row[@Gender=$g]/@Age)
                return <li>{data($a)}
                <ul>
                {
                    for $n in //root/row
                    where $n/@Gender=$g and $n/@Age = $a
                        return <li>{data($n/@Name)}</li>
                }</ul></li>
            }
            </ul> 
        </li>
    </ul>
') AS Result;

Results:

Source
----------------------------------------------------
<root>
  <row Gender="Male" Age="30" Name="Bill" />
  <row Gender="Female" Age="27" Name="Jenny" />
  <row Gender="Female" Age="27" Name="Debby" />
  <row Gender="Male" Age="44" Name="Frank" />
</root>

Result
----------------------------------------------------
<ul><li>Female<ul><li>27<ul><li>Jenny</li><li>Debby</li></ul></li></ul></li></ul>
<ul><li>Male<ul><li>30<ul><li>Bill</li></ul></li><li>44<ul><li>Frank</li></ul></li></ul></li></ul>

@Bogdan, you've listed the number 27 twice. These should be included in the same
node, and 27 should only be shown on screen once. Please look closer at the desired output. — Aaron Bertrand, Apr 24 '12 at 15:33

Return Select Statement as formatted HTML

2 Answers2

Linked