如何在SQLite中填充交叉表数值？已生成表头但无法填充内容

Hey there! I get it—having a crosstab that only shows headers but no data is super frustrating. Let's break down how to fix this, since SQLite doesn't have a built-in PIVOT function like some other databases, so we need to use conditional aggregation instead.

First, let's confirm your initial avg_salary view looks solid—it groups by team and season to calculate average salaries, which is exactly the base data we need for the crosstab. Let's start by verifying that view actually has data: run this query to check:

SELECT * FROM avg_salary LIMIT 10;

If you see rows with team names, seasons, and average salary values here, we're good to move on. The problem is almost certainly in how you're building the crstab_avg_salary view.

The Correct Way to Build a SQLite Crosstab #

Since SQLite lacks PIVOT, we use CASE WHEN clauses paired with an aggregation function (like MAX or AVG) to turn rows into columns. Here's a complete example tailored to your data:

CREATE VIEW crstab_avg_salary AS
SELECT 
    team AS 'Team',
    -- Replace these season values with the actual seasons present in your dataset
    MAX(CASE WHEN season = '2018' THEN avg_salary END) AS '2018_Avg_Salary',
    MAX(CASE WHEN season = '2019' THEN avg_salary END) AS '2019_Avg_Salary',
    MAX(CASE WHEN season = '2020' THEN avg_salary END) AS '2020_Avg_Salary',
    MAX(CASE WHEN season = '2021' THEN avg_salary END) AS '2021_Avg_Salary'
FROM avg_salary
GROUP BY team;

Why This Works:

• The CASE WHEN checks each row's season and returns the average salary only if it matches the target season (otherwise it returns NULL).
• MAX (or AVG) ignores NULL values, so for each team, it pulls the single valid average salary for each season.
• GROUP BY team ensures we get one row per team, with all season columns populated.

Troubleshooting Tips #

• If you still don't see data, double-check that the season values in your CASE WHEN clauses exactly match the season values in your avg_salary view (capitalization, formatting, and spelling matter!).
• Make sure you're including the GROUP BY team clause—without it, you'll get a single row with aggregated values across all teams, not per-team data.

Once you adjust your view to use this structure, your crosstab should populate with the average salary values for each team and season.

内容的提问来源于stack exchange，提问作者Lacri Mosa