We help IT Professionals succeed at work.

Creating a Table of Contents hirearchy with SQL and Coldfusion

High Priority
166 Views
Last Modified: 2019-09-09
I'm working on creating a specific table of contents structure on a web page, from data in a SQL database. The display includes indenting levels and prefixed symbols. The symbols, in order are:
Roman numerals, Upper case letters, Numbers, Lower case letters, Numbers in parenthesis, Lower case letters in parenthesis.
-------------------------------
I. yadda
     A. yadda
     B. yadda
         1. yadda
               a. yadda
               b. yadda
         2. yadda
               a. yadda
               b. yadda
                     (1) yadda
                     (2) yadda
                              (a) yadda
                              (b) yadda

II. yadda
------------------------------------

I'd like some help in creating this format with dynamic data. The indention and the top level Roman Numbers are working fine but it's the recursion of all the other symbols is where I could use a little help.  

Here are the sql fields I'm returning:

indentAmount, headingCount, headingName, headingText

indentAmount is the number of levels to indent. example: 0, 1, 2, 3, etc..
headingCount is the number of items for a given heading. example: 1, 2, 3. etc..
headingName is the actual name of the heading. example H2, H3, H4... etc.
headingText is the heading text itself.  In my example it's "Yadda".

Here is the query I'm running:

select h.sectionID,
CASE
WHEN h.HeadingTitle = 'Heading2' THEN 0
WHEN h.HeadingTitle = 'Heading3' THEN 1
WHEN h.HeadingTitle = 'Heading4' THEN 2
WHEN h.HeadingTitle = 'Heading5' THEN 3 END indentAmt,
ROW_NUMBER()OVER(PARTITION BY HeadingTitle ORDER BY h.ID) AS HedCount,
h.HeadingTitle, h.Heading, L.phrase
from sectionHeading h
left join Lookup L on h.sectionID = L.sectionId
left join claim c on h.domainId = c.domain AND (SELECT Index1 FROM Connect(l.phrase, c.claim)) = 1 where c.dateCreated >= '2019-09-04'
and c.createdBy = 12345 and h.domainID = 3 order by h.id

Here is some sample data output:

 HEADING HEADINGTITLE HEDCOUNT INDENTAMT PHRASE SECTIONID

1 Antibodies  Heading2  1  0  CDR  923  
2 Types of Antibodies  Heading3  1  1  CDR  925  
3 Functional Antibody Fragments and Antigen-Binding Fragments  Heading3  2  1  CDR  926  
4 Antigen-Binding Fragments  Heading4  1  2  CDR  926  
5 Fragment Crystallizable Region, Fc  Heading4  2  2  CDR  926  
6 Polypeptides with antibody CDRs & Scaffolding Domains that Display the CDRs  Heading3  3  1  CDR  927  
7 Antibody Binding  Heading3  4  1  CDR  928  
8 Affinity/Avidity  Heading4  3  2  CDR  928  
9 Epitope Specificity  Heading4  4  2  CDR  928  
10 Modification of Antibody Antigen-Binding Domains  Heading4  5  2  CDR  928  
11 Chemical Modification of Antibodies  Heading3  5  1  CDR  929  
12 Conjugation  Heading4  6  2  CDR  929  
13 Conjugate Types  Heading5  1  3  CDR  929  
14 Conjugation Methodology  Heading5  2  3  CDR  929  
15 Proteins  Heading3  6  1  CDR  930  
16 Variant Polypeptides  Heading4  7  2  CDR  930  
17 Considerations for Substitutions  Heading4  8  2  CDR  930  
18 Nucleic Acids  Heading3  7  1  CDR  931  
19 Hybridization  Heading4  9  2  CDR  931  
20 Mutation  Heading4  10  2  CDR  931  
21 Probes  Heading4  11  2  CDR  931  
22 Antibody Production  Heading2  2  0  CDR  932  
23 Antibody Production  Heading3  8  1  CDR  932  
24 Fully Human Antibody Production  Heading3  9  1  CDR  932  
25 Antibody Fragments Production  Heading3  10  1  CDR  932  
26 Obtaining Encoded Antibodies  Heading2  3  0  CDR  933  
27 Expression  Heading3  11  1  CDR  933  
28 Vectors  Heading4  12  2  CDR  933  
29 Expression Systems  Heading4  13  2  CDR  933  
30 Methods of Gene Transfer  Heading4  14  2  CDR  933  
31 Host Cells  Heading4  15  2  CDR  933  
32 Isolation  Heading3  12  1  CDR  933  
33 Checkpoint Inhibitors and Combination Treatment  Heading3  13  1  immunotherapy  936  
34 PD-1, PDL1, and PDL2 inhibitors  Heading4  16  2  immunotherapy  936  
35 CTLA-4, B7-1, and B7-2  Heading4  17  2  immunotherapy  936  
36 Immunotherapy  Heading2  4  0  Immunotherapy  937  
37 Inhibition of co-stimulatory molecules  Heading3  14  1  Immunotherapy  937  
38 Dendritic cell therapy  Heading3  15  1  Immunotherapy  937  
39 CAR-T cell therapy  Heading3  16  1  Immunotherapy  937  
40 Cytokine therapy  Heading3  17  1  Immunotherapy  937  
41 Adoptive T-cell therapy  Heading3  18  1  Immunotherapy  937  


Here is the html output:


<table>
<tr>
      <th>Keyword/Phrase</th>
      <th>indentAmt</th>
      <th>Heading count</th>
      <th>Type</th>
      <th>Heading text<th>
</tr>

<tr>
      <td>CDR</td>
      <td>0</td>
      <td>1</td>
      <td>Heading2</td>
      <td>
            <div style="text-indent:000px">
                  I. Antibodies
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>1</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Types of Antibodies
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>2</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Functional Antibody Fragments and Antigen-Binding Fragments
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>1</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Antigen-Binding Fragments
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>2</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Fragment Crystallizable Region, Fc
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>3</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Polypeptides with antibody CDRs &amp; Scaffolding Domains that Display the CDRs
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>4</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Antibody Binding
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>3</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Affinity/Avidity
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>4</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Epitope Specificity
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>5</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Modification of Antibody Antigen-Binding Domains
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>5</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Chemical Modification of Antibodies
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>6</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Conjugation
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>3</td>
      <td>1</td>
      <td>Heading5</td>
      <td>
            <div style="text-indent:300px">
                  Conjugate Types
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>3</td>
      <td>2</td>
      <td>Heading5</td>
      <td>
            <div style="text-indent:300px">
                  Conjugation Methodology
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>6</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Proteins
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>7</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Variant Polypeptides
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>8</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Considerations for Substitutions
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>7</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Nucleic Acids
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>9</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Hybridization
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>10</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Mutation
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>11</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Probes
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>0</td>
      <td>2</td>
      <td>Heading2</td>
      <td>
            <div style="text-indent:000px">
                  II. Antibody Production
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>8</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Antibody Production
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>9</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Fully Human Antibody Production
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>10</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Antibody Fragments Production
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>0</td>
      <td>3</td>
      <td>Heading2</td>
      <td>
            <div style="text-indent:000px">
                  III. Obtaining Encoded Antibodies
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>11</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Expression
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>12</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Vectors
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>13</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Expression Systems
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>14</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Methods of Gene Transfer
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>2</td>
      <td>15</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  Host Cells
            </div>
      </td>
</tr>

<tr>
      <td>CDR</td>
      <td>1</td>
      <td>12</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Isolation
            </div>
      </td>
</tr>

<tr>
      <td>immunotherapy</td>
      <td>1</td>
      <td>13</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Checkpoint Inhibitors and Combination Treatment
            </div>
      </td>
</tr>

<tr>
      <td>immunotherapy</td>
      <td>2</td>
      <td>16</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  PD-1, PDL1, and PDL2 inhibitors
            </div>
      </td>
</tr>

<tr>
      <td>immunotherapy</td>
      <td>2</td>
      <td>17</td>
      <td>Heading4</td>
      <td>
            <div style="text-indent:200px">
                  CTLA-4, B7-1, and B7-2
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>0</td>
      <td>4</td>
      <td>Heading2</td>
      <td>
            <div style="text-indent:000px">
                  IV. Immunotherapy
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>1</td>
      <td>14</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Inhibition of co-stimulatory molecules
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>1</td>
      <td>15</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Dendritic cell therapy
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>1</td>
      <td>16</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  CAR-T cell therapy
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>1</td>
      <td>17</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Cytokine therapy
            </div>
      </td>
</tr>

<tr>
      <td>Immunotherapy</td>
      <td>1</td>
      <td>18</td>
      <td>Heading3</td>
      <td>
            <div style="text-indent:100px">
                  Adoptive T-cell therapy
            </div>
      </td>
</tr>
</table>


Please let me know if you need more to go on.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
ROW_NUMBER()OVER(PARTITION BY HeadingTitle ORDER BY h.ID) AS HedCount,

It'd be easier if the row numbers were generated by parent / level (i.e. INDENTAMT). So each time the parent/level changed, the row numbers restarted at 1

I. GrandParent 1                       ==> row 1    (level 0)

   A. Parent 1                              ==> row 1    (level 1)
   B. Parent 2                              ==> row 2     (level 1)

           1.  Child                            ==> row 1   (level 2)
            ....

   C. Parent 3                              ==> row 3    (level 1)

II. GrandParent 2                      ==> row 2    (level 0)

Then you could create and apply a function to generate the numbering based on the current level - and row number.

Author

Commented:
That makes sense but how does the change in parent/level get tracked? You would have to look back at the parent somehow. I'm not sure how to implement that looping pattern that builds the "row" column.
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
One way is using a CTE to build a tree

DDL + Sample Data
DECLARE @testTable TABLE
(
  nodeId int
  , nodeName varchar(100)
  , parentId int
)

INSERT INTO @testTable (nodeId, nodeName, parentId)
VALUES
(1, 'GrandParent 1', null)
, (2, 'Parent 1', 1)
, (3, 'Parent 2', 1)
, (4, 'Child', 3)
, (5, 'Parent 3', 1)
, (6, 'GrandParent 2', null)

Open in new window


SQL

;WITH tree 
AS  
(  
	SELECT	nodeId, nodeName, parentId, 0 AS levelNum
	FROM	@testTable  
	WHERE	parentId is null  
	UNION ALL
	SELECT  c.nodeId, c.NodeName, c.parentId, t.[levelNum]+1
	FROM    @testTable c  INNER JOIN tree t on c.parentId = t.nodeId  
)  
SELECT	ROW_NUMBER() OVER( PARTITION BY levelNum ORDER BY nodeId) AS RowNum
		, *
FROM	tree
ORDER BY nodeId  

Open in new window


Results

RowNum|nodeId|nodeName|parentId|levelNum
1|1|GrandParent 1|NULL|0
1|2|Parent 1|1|1
2|3|Parent 2|1|1
1|4|Child|3|2
3|5|Parent 3|1|1
2|6|GrandParent 2|NULL|0

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Any luck with building a tree? Does your table have a parent/child relationship? i.e.  Columns - MainID (child), ParentID (references MainID)

Author

Commented:
Thanks _agx_, That's exactly what I was looking for. It seems the parent lookup wasn't as bad as I thought. Thanks again!!!
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
You're welcome.  It's simpler now.  It used to be quite arduous before row_number() and cte's were introduced back when ;-) Just be aware there's a default limit of 100 levels deep. So if you think you'll need more than that, you'll have to specify a maximumrecursion value (0 means unlimited)

BTW, I was thinking of something like this for generating letters ie  "A","B",... "AA","BB",.., "AAA","BBB",....

	
<cfscript>
	function genarateTOCLetter( required numeric rowNumber ) 
	{
		// calculates next letter A,B,C....Z
		var letter = chr( 65 + ((arguments.rowNumber-1) mod 26));
		// repeat letter however many times are needed
		var length   = ceiling( arguments.rowNumber / 26 );

		return repeatString(letter, length);
	}
</cfscript>

Open in new window


Then use it in a switch/case based on level and row number

       - level 0 => (Roman numerals)     => yourRomanNumeralFunction( rowNumber )
       - level 1 => (Upper Case Letters)  => genarateTOCLetter( rowNumber )
       - level 2 => (Plain numbers)          => rowNumber
       - level 3 => (Lower Case Letters)  => lcase( genarateTOCLetter( rowNumber ) )
       ... etc...