create html table with json data

Question

I have received json response and I want to create html table with data I want to create a table as

Geo       Identified    Closed Won   Qualified  Total
NSU        20                0          0        20 
US East    50                8          3        61
US West    39                2          16       57
Total     109                10         19       138

My json response is

[{Geo: "US West", SalesStage: "Closed Won", count: 2},
 {Geo: "US East", SalesStage: "Closed Won", count: 8},
 {Geo: "US West", SalesStage: "Qualified", count: 16},
 {Geo: "US East", SalesStage: "Qualified", count: 3},
 {Geo: "US East", SalesStage: "Identified", count: 50},
 {Geo: "US West", SalesStage: "Identified", count: 39},
 {Geo: "NSU", SalesStage: "Identified", count: 20}   ]

I have tried as

const summary_data = [{Geo: "US West", SalesStage: "Closed Won", count: 2},
     {Geo: "US East", SalesStage: "Closed Won", count: 8},
     {Geo: "US West", SalesStage: "Qualified", count: 16},
     {Geo: "US East", SalesStage: "Qualified", count: 3},
     {Geo: "US East", SalesStage: "Identified", count: 50},
     {Geo: "US West", SalesStage: "Identified", count: 39},
     {Geo: "NSU", SalesStage: "Identified", count: 20}   ];

$.each(summary_data, function (index, item) {
    const $tr = $('<tr>');
  $tr.append([item.Geo, item.SalesStage, item.count].map(x => $('<td>').text(x)));
  $('tbody').append($tr);
});

table {
  border-collapse: collapse;
}

td {
  border: 1px solid black;
  padding: 0.5em;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table>
  <thead>
  
  </thead>
  <tbody>
  </tbody>
</table>

@Jeremy The OP wants the transposed and aggregated result. I'm sure there's a duplicate, just needs finding... — Heretic Monkey, Jul 06 '20 at 15:14
Well, if the data were in a 2D array, there's [Transposing a 2D-array in JavaScript](https://stackoverflow.com/q/17428587/215552)... — Heretic Monkey, Jul 06 '20 at 15:52
See also [How to invert (transpose) the rows and columns of an HTML table?](https://stackoverflow.com/q/6297591/215552) — Heretic Monkey, Jul 06 '20 at 16:22

blex · Accepted Answer · 2020-07-06T15:46:41.630

Note: I'm not proud of this answer, it's not very readable. If you find a cleaner way...

You need to rearrange your data. You could break it down with reduce. The solution below is pretty ugly though...

const summary_data = [{Geo:"US West",SalesStage:"Closed Won",count:2},{Geo:"US East",SalesStage:"Closed Won",count:8},{Geo:"US West",SalesStage:"Qualified",count:16},{Geo:"US East",SalesStage:"Qualified",count:3},{Geo:"US East",SalesStage:"Identified",count:50},{Geo:"US West",SalesStage:"Identified",count:39},{Geo:"NSU",SalesStage:"Identified",count:20}];

// Gather the headers we'll have in the end table
const stages = summary_data.reduce((res, row) => {
  if (!res.includes(row.SalesStage)) res.push(row.SalesStage);
  return res;
}, []).concat('Total'); // ["Closed Won", "Qualified", "Identified", "Total"]

// Gather every field for each Geo
const obj = summary_data.reduce((res, row) => {
  if (!res[row.Geo]) {
    res[row.Geo] = Object.fromEntries([ ['Geo', row.Geo], ...stages.map(s => [s, 0]) ]);
  }
  res[row.Geo][row.SalesStage] += row.count;
  res[row.Geo].Total += row.count;
  return res;
}, {}); // { "US West": {}, "US East": {}, ... }

const data = Object.values(obj); // [ { "Geo": "US West", "Closed Won": 2, ...}, ...]

// Add a "Total" row
data.push({
  Geo: 'Total',
  ...Object.fromEntries(
    stages.map(s => [s, data.reduce((sum, row) => row[s] + sum, 0)])
  )
});

$('thead').append(
  Object.keys(data[0]).map(x => $('<td>').text(x))
);
$('tbody').append(
  data.map(row => $('<tr>').append(
     Object.values(row).map(x => $('<td>').text(x))
  ))
);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><table> <thead> </thead> <tbody> </tbody></table><style>table{border-collapse:collapse}td{border:1px solid #000;padding:.5em}</style>

I need one more table for user based simillar to this I have modified some thing and I have created the column.but I want in order username,geo,Qualified,Proposal submitted,total.now I received the order as username,Qualified,Proposal submitted,total,geo https://jsfiddle.net/7bz2o89k/3/.Plz help — user3386779, Jul 07 '20 at 10:48
@user3386779 Sure. I edited line 24, 28 and 40: https://jsfiddle.net/e0tq7wup/ — blex, Jul 07 '20 at 10:59
genius! I need one more help. I have pipelinevalue in the response and I want to get the sum of the pipeline value.I did the count only for propose submitted,qualified and negotiated contracting as expected.I want to show the horizontal total and pipelinevalue only I have the three values. Is it possible to arrange in identified,qualified Total,pipeline value close won in horizontal. https://jsfiddle.net/2s3ro1tq/ . Bit urgent man please help. — user3386779, Jul 09 '20 at 06:41
I'm sorry but I'm going vacation and I'm on the road right now, I won't be able to help. If you're stuck, you can post a new question, others will be able to answer. Good luck — blex, Jul 09 '20 at 07:13
can you please help me to do https://stackoverflow.com/questions/63168933/craete-the-html-table-with-json-data — user3386779, Jul 30 '20 at 08:25

create html table with json data

1 Answers1