Merge and grouping table using JSON and JQuery

Question

I have problems giving rowspan to a table and grouped them based on parent row .I already tried to find some solution,but most of solution is to merge same values and not grouping them.

  var data = 
  [
    {
      "LEVEL_1": "LEVEL 1",
      "LEVEL_2": "LEVEL 1.1",
      "LEVEL_3": "LEVEL 1.1.1",
    },
    {
      "LEVEL_1": "LEVEL 1",
      "LEVEL_2": "LEVEL 1.2",
      "LEVEL_3": "LEVEL 1.2.1",
    },
     {
      "LEVEL_1": "LEVEL 1",
      "LEVEL_2": "LEVEL 1.2",
      "LEVEL_3": "LEVEL 1.2.2",
    },
    {
      "LEVEL_1": "LEVEL 2",
      "LEVEL_2": "LEVEL 2.1",
      "LEVEL_3": "LEVEL 2.1.1",
    },
    {
      "LEVEL_1": "LEVEL 2",
      "LEVEL_2": "LEVEL 2.1",
      "LEVEL_3": "LEVEL 2.1.2",
    },
    {
      "LEVEL_1": "LEVEL 2",
      "LEVEL_2": "LEVEL 2.2",
      "LEVEL_3": "LEVEL 2.2.1",
    },
    {
      "LEVEL_1": "LEVEL 2",
      "LEVEL_2": "LEVEL 2.2",
      "LEVEL_3": "LEVEL 2.2.2",
    }
  ];

  var tableStr = '';
  $.each(data, function(index, value) { 
    tableStr += '<tr>' + 
       '<td>'+value.LEVEL_1+'</td>'+
       '<td>'+value.LEVEL_2+'</td>'+
       '<td>'+value.LEVEL_3+'</td>'+
     '</tr>';
  }); 
  $('#user tbody').html(tableStr);

 table { 
      border-collapse: collapse;
    }

    td {
      padding: 20px; 
      border: 1px solid black; 
      text-align: center;
    }

    th {
      padding: 20px; 
      border: 1px solid black; 
      text-align: center;
    }

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="user">
    <thead>
        <tr>
          <th>LEVEL 1</th>
          <th>LEVEL 2</th>
          <th>LEVEL 3</th>
        </tr>
        <tr>
        </tr>
    </thead>
    <tbody> 
    </tbody>
</table>

The result i was expecting :

I always found a result,it worked in the beginning. But when i added another level,but same data,it always looked like this

The JSON Data provided from Ajax request,was already given like that .Do i have to modify the JSON data and grouped them ? or it can be done using the given JSON Data ?How to do this using Jquery and JSON data ? Thanks in advance

check https://stackoverflow.com/questions/3838488/html-table-different-number-of-columns-in-different-rows — AZ_, Mar 27 '19 at 07:57
i already checked the page . but the answer from the page is only normal HTML . i need give the rowspan using jquery @AZ_ — Bakti Wijaya, Mar 27 '19 at 08:09

Abhishek Pandey · Answer 1 · 2019-03-27T09:26:29.123

With the help of this question I have managed to merge 1st level.

var data = [{
    "LEVEL_1": "LEVEL 1",
    "LEVEL_2": "LEVEL 1.1",
    "LEVEL_3": "LEVEL 1.1.1",
  },
  {
    "LEVEL_1": "LEVEL 1",
    "LEVEL_2": "LEVEL 1.2",
    "LEVEL_3": "LEVEL 1.2.1",
  },
  {
    "LEVEL_1": "LEVEL 1",
    "LEVEL_2": "LEVEL 1.2",
    "LEVEL_3": "LEVEL 1.2.2",
  },
  {
    "LEVEL_1": "LEVEL 2",
    "LEVEL_2": "LEVEL 2.1",
    "LEVEL_3": "LEVEL 2.1.1",
  },
  {
    "LEVEL_1": "LEVEL 2",
    "LEVEL_2": "LEVEL 2.1",
    "LEVEL_3": "LEVEL 2.1.2",
  },
  {
    "LEVEL_1": "LEVEL 2",
    "LEVEL_2": "LEVEL 2.2",
    "LEVEL_3": "LEVEL 2.2.1",
  },
  {
    "LEVEL_1": "LEVEL 2",
    "LEVEL_2": "LEVEL 2.2",
    "LEVEL_3": "LEVEL 2.2.2",
  }
];

for (i = 0; i < data.length; i++) {
  var l1 = data[i].LEVEL_1;
  data[i].rowspan = 1;
  for (j = i + 1; j < data.length; j++) {
    var l2 = data[j].LEVEL_1;
    if (l1 == l2) {
      data[i].rowspan += 1;
    } else {
      break;
    }
  }
  i = j - 1;
}
var tableStr = '';
$.each(data, function(index, value) {
  if (value.rowspan > 1) {
    tableStr += '<tr>' +
      '<td rowspan="' + value.rowspan + '">' + value.LEVEL_1 + '</td>' +
      '<td>' + value.LEVEL_2 + '</td>' +
      '<td>' + value.LEVEL_3 + '</td>' +
      '</tr>';
  } else {
    tableStr += '<tr>' +
      '<td>' + value.LEVEL_2 + '</td>' +
      '<td>' + value.LEVEL_3 + '</td>' +
      '</tr>';
  }


});
$('#user tbody').html(tableStr);

table {
  border-collapse: collapse;
}

td {
  padding: 20px;
  border: 1px solid black;
  text-align: center;
}

th {
  padding: 20px;
  border: 1px solid black;
  text-align: center;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="user">
  <thead>
    <tr>
      <th>LEVEL 1</th>
      <th>LEVEL 2</th>
      <th>LEVEL 3</th>
    </tr>
    <tr>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>

yunzen · Answer 2 · 2019-03-28T10:57:37.843

I have a solution for you. It may not be the most elegant, but it works

At first I transform the data from JSON to a nested object, which counts the appearance of lower level items.

The second step is to traverse that nested object and create table, tr, and td accordingly with count values from step 1 used as rowspan attribute values.

I don't even need jQuery, just VanillaJS.

console.clear();

var data = [
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.1",
    LEVEL_3: "LEVEL 1.1.1"
  },
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.2",
    LEVEL_3: "LEVEL 1.2.1"
  },
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.2",
    LEVEL_3: "LEVEL 1.2.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.1",
    LEVEL_3: "LEVEL 2.1.1"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.1",
    LEVEL_3: "LEVEL 2.1.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.2",
    LEVEL_3: "LEVEL 2.2.1"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.2",
    LEVEL_3: "LEVEL 2.2.2"
  }
];

const transformData = (data) => {
  let t = {};

  data.forEach((item, index, source) => {
    let l1 = item.LEVEL_1,
        l2 = item.LEVEL_2,
        l3 = item.LEVEL_3;
    t[l1]                         = t[l1]                         || {text: l1, content: {}, count: 0};
    t[l1].content[l2]             = t[l1].content[l2]             || {text: l2, content: {}, count: 0};
    t[l1].content[l2].content[l3] = t[l1].content[l2].content[l3] || {text: l3, content: {}, count: 0};
    t[l1].count++
    t[l1].content[l2].count++
    t[l1].content[l2].content[l3].count++
  })
  return t;
}

const transformDataTable = (tdata) => {

  const table = document.createElement('table');

  for (l1 in tdata) {
    const td1 = document.createElement('th')
    td1.textContent = tdata[l1].text;
    td1.setAttribute('rowspan', tdata[l1].count)
    let done1 = false;

    for (l2 in tdata[l1].content) {
      const td2 = document.createElement('td')
      td2.textContent = tdata[l1].content[l2].text;
      td2.setAttribute('rowspan', tdata[l1].content[l2].count)
      let done2 = false;
      
      for (l3 in tdata[l1].content[l2].content) {
        const td3 = document.createElement('td')
        td3.textContent = tdata[l1].content[l2].content[l3].text;
        td3.setAttribute('rowspan', tdata[l1].content[l2].content[l3].count)
        const tr = document.createElement('tr')
        !done1 && tr.appendChild(td1) && (done1 = true);
        !done2 && tr.appendChild(td2) && (done2 = true);
        tr.appendChild(td3);
        table.appendChild(tr);
      }
    }
  }
  return table;
}


const tdata = transformData(data);
const table = transformDataTable(tdata);


document.body.appendChild(table)

table {
  border-collapse: collapse;
}

td,
th {
  padding: 20px;
  border: 1px solid black;
  text-align: center;
}

With some modifications you can sort every column on it's own

console.clear();

var data = [
  {
    LEVEL_1: "LEVEL 3",
    LEVEL_2: "LEVEL 3.1",
    LEVEL_3: "LEVEL 3.1.1"
  },
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.1",
    LEVEL_3: "LEVEL 1.1.1"
  },
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.2",
    LEVEL_3: "LEVEL 1.2.1"
  },
  {
    LEVEL_1: "LEVEL 1",
    LEVEL_2: "LEVEL 1.2",
    LEVEL_3: "LEVEL 1.2.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.1",
    LEVEL_3: "LEVEL 2.1.1"
  },
  {
    LEVEL_1: "LEVEL 3",
    LEVEL_2: "LEVEL 3.1",
    LEVEL_3: "LEVEL 3.1.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.1",
    LEVEL_3: "LEVEL 2.1.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.2",
    LEVEL_3: "LEVEL 2.2.1"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.2",
    LEVEL_3: "LEVEL 2.2.2"
  },
  {
    LEVEL_1: "LEVEL 2",
    LEVEL_2: "LEVEL 2.2",
    LEVEL_3: "LEVEL 2.2.3"
  }
  
];

const transformData = (data) => {
  let t = {};

  data.forEach((item, index, source) => {
    let l1 = item.LEVEL_1,
        l2 = item.LEVEL_2,
        l3 = item.LEVEL_3;
    t[l1]                         = t[l1]                         || {text: l1, content: {}, count: 0};
    t[l1].content[l2]             = t[l1].content[l2]             || {text: l2, content: {}, count: 0};
    t[l1].content[l2].content[l3] = t[l1].content[l2].content[l3] || {text: l3, content: {}, count: 0};
    t[l1].count++
    t[l1].content[l2].count++
    t[l1].content[l2].content[l3].count++
  })
  return t;
}

const transformDataTable = (tdata, sort = (a,b) => b-a, sort1 = sort, sort2 = sort1) => {

  const table = document.createElement('table');

  for (l1 of Object.keys(tdata).sort(sort)) {
    const td1 = document.createElement('th')
    td1.textContent = tdata[l1].text;
    td1.setAttribute('rowspan', tdata[l1].count)
    let done1 = false;

    for (l2 of Object.keys(tdata[l1].content).sort(sort1)) {
      const td2 = document.createElement('td')
      td2.textContent = tdata[l1].content[l2].text;
      td2.setAttribute('rowspan', tdata[l1].content[l2].count)
      let done2 = false;
      
      for (l3 of Object.keys(tdata[l1].content[l2].content).sort(sort2)) {
        const td3 = document.createElement('td')
        td3.textContent = tdata[l1].content[l2].content[l3].text;
        td3.setAttribute('rowspan', tdata[l1].content[l2].content[l3].count)
        const tr = document.createElement('tr')
        !done1 && tr.appendChild(td1) && (done1 = true);
        !done2 && tr.appendChild(td2) && (done2 = true);
        tr.appendChild(td3);
        table.appendChild(tr);
      }
    }
  }
  return table;
}

const asc = (a,b) => b-a
const desc = (a,b) => a-b

const tdata = transformData(data);
const table = transformDataTable(tdata, desc, asc);


document.body.appendChild(table)

table {
  border-collapse: collapse;
}

td,
th {
  padding: 20px;
  border: 1px solid black;
  text-align: center;
}

score 0 · Answer 3 · answered Mar 27 '19 at 14:48

I broke that down into two steps: 1. generate a tree like structure from the input data to allow rowspan counting; 2. drawing the table rows and columns.

The whole code is as generic as possible to avoid hardcoded column counts.

I also added a dynamic table header. If you add a another column to the data-structure, the HTML table headings will get updated as well. For example it is possible using this same code to add a fourth level, e.g. "LEVEL_4": "LEVEL 1.2.2.1" to one or many rows.

var data = [
    {
        "LEVEL_1": "LEVEL 1",
        "LEVEL_2": "LEVEL 1.1",
        "LEVEL_3": "LEVEL 1.1.1"
    },
    {
        "LEVEL_1": "LEVEL 1",
        "LEVEL_2": "LEVEL 1.2",
        "LEVEL_3": "LEVEL 1.2.1"
    },
    {
        "LEVEL_1": "LEVEL 1",
        "LEVEL_2": "LEVEL 1.2",
        "LEVEL_3": "LEVEL 1.2.2"
    },
    {
        "LEVEL_1": "LEVEL 2",
        "LEVEL_2": "LEVEL 2.1",
        "LEVEL_3": "LEVEL 2.1.1"
    },
    {
        "LEVEL_1": "LEVEL 2",
        "LEVEL_2": "LEVEL 2.1",
        "LEVEL_3": "LEVEL 2.1.2"
    },
    {
        "LEVEL_1": "LEVEL 2",
        "LEVEL_2": "LEVEL 2.2",
        "LEVEL_3": "LEVEL 2.2.1"
    },
    {
        "LEVEL_1": "LEVEL 2",
        "LEVEL_2": "LEVEL 2.2",
        "LEVEL_3": "LEVEL 2.2.2"
    }
];

/**
 * Generate rows and columns for the table using the JSON data.
 *
 * @param {Object} data
 * @param {HTMLTableSectionElement} tableBody
 * @param {HTMLTableSectionElement} tableHead
 */
function setTableContent(data, tableBody, tableHead) {
    /* The rowspan is stored here depending on the label of each column */
    var columnRowspans = {};

    var columnHeadings = {};

    /**
     * Translate the data into a tree-like structure
     *
     * @param {JSON} data
     * @return {Array}
     */
    function translateData(data) {
        var rows = [];
        /* Iterate over each row in the dataset */
        data.forEach(function (row) {
            var columns = [],
                label;
            /* Find the individual columns */
            for (var key in row) {
                /* Store the columns header */
                columnHeadings[key] = true;

                label = row[key];
                /* Skip those columns that were already added */
                if (columnRowspans[label]) {
                    columnRowspans[label]++;
                    continue;
                }
                columns.push(label);
                columnRowspans[label] = 1;
            }
            rows.push(columns);
        });
        return rows;
    }

    /* Template string used for a single field in the table */
    var cellTemplate = '<td rowspan="{rowspan}">{content}</td>';

    /* Output */
    var html = '';
    translateData(data).forEach(function (row, index) {
        html += '<tr>';
        row.forEach(function (columnLabel) {
            /* Use the stored rowspans here to generate columns */
            html += cellTemplate
                .replace('{rowspan}', columnRowspans[columnLabel])
                .replace('{content}', columnLabel);
        });
        html += '</tr>';
    });

    if (tableBody instanceof HTMLTableSectionElement) {
        tableBody.innerHTML = html;
    }

    if (tableHead instanceof HTMLTableSectionElement) {
        var thead = '<tr>';
        Object.keys(columnHeadings).forEach(function (heading) {
            thead += '<th>' + heading.replace('_', ' ') + '</th>';
        });
        thead += '</tr>';
        tableHead.innerHTML = thead;
    }
}

setTableContent(data, document.querySelector('#user tbody'), document.querySelector('#user thead'));

 table { 
      border-collapse: collapse;
    }

    td {
      padding: 20px; 
      border: 1px solid black; 
      text-align: center;
    }

    th {
      padding: 20px; 
      border: 1px solid black; 
      text-align: center;
    }

<table id="user">
    <thead>
    </thead>
    <tbody> 
    </tbody>
</table>

i almost find this answer correct.but i run some tests,and adding new JSON data {LEVEL_1 : "LEVEL 3", LEVEL_2 : "2.2","LEVEL_3" : "2.2.2"} and LEVEL 2.2 inside LEVEL 3 ,merging into LEVEL 2.2 on LEVEL 2 — Bakti Wijaya, Mar 28 '19 at 03:55

Merge and grouping table using JSON and JQuery

3 Answers3