Pivot table data in Laravel

Introduction

A pivot table helps you quickly summarize and analyze large sets of data by grouping and counting or totaling values.

You want to see total sales by product category and month.

You need to count how many times each item appears in a list.

You want to compare expenses by department and quarter.

You want to find the average score for students by class.

You want to organize survey results by question and answer.

Syntax

Laravel

PivotTable::create($data)
    ->rows(['row_field'])
    ->columns(['column_field'])
    ->values('value_field', 'sum')
    ->make();

rows sets the fields to group data by rows.

columns sets the fields to group data by columns.

values sets the field to aggregate and the type of aggregation like sum, count, average.

Examples

This groups sales data by product and month, summing the amounts.

Laravel

PivotTable::create($salesData)
    ->rows(['Product'])
    ->columns(['Month'])
    ->values('Amount', 'sum')
    ->make();

This counts how many responses each answer got for each question.

Laravel

PivotTable::create($surveyData)
    ->rows(['Question'])
    ->columns(['Answer'])
    ->values('ResponseID', 'count')
    ->make();

Sample Program

This example creates a pivot table that sums sales amounts by product and month.

Laravel

<?php
use PivotTable;

$salesData = [
    ['Product' => 'Apple', 'Month' => 'Jan', 'Amount' => 100],
    ['Product' => 'Apple', 'Month' => 'Feb', 'Amount' => 150],
    ['Product' => 'Banana', 'Month' => 'Jan', 'Amount' => 200],
    ['Product' => 'Banana', 'Month' => 'Feb', 'Amount' => 100],
];

$pivot = PivotTable::create($salesData)
    ->rows(['Product'])
    ->columns(['Month'])
    ->values('Amount', 'sum')
    ->make();

print_r($pivot);

Important Notes

Make sure your data is clean and consistent for accurate pivot results.

You can change aggregation types like 'sum', 'count', or 'average' depending on your need.

Pivot tables help you see patterns and summaries without changing your original data.

Summary

Pivot tables group and summarize data quickly.

Use rows and columns to organize data categories.

Choose the right aggregation to get sums, counts, or averages.