如何编写SELECT语句实现OrderingTAB表的行转列（透视）查询

Hey there! Let's figure out how to get that transposed, grouped result you're looking for. Your goal is to turn the depar values into columns, with each location as a row showing the corresponding pops numbers—here are a few solid solutions depending on your database system:

Universal Solution (Works in Almost All SQL Databases) #

If you need something compatible with MySQL, PostgreSQL, SQLite, and more, use CASE WHEN with an aggregation function. Since each location + depar pair is unique in your data, either MAX() or SUM() will work to grab the correct pops value:

SELECT 
    location,
    MAX(CASE WHEN depar = 'a-team' THEN pops END) AS "A-team",
    MAX(CASE WHEN depar = 'b-team' THEN pops END) AS "B-team",
    MAX(CASE WHEN depar = 'c-team' THEN pops END) AS "C-team",
    MAX(CASE WHEN depar = 'd-team' THEN pops END) AS "D-team",
    MAX(CASE WHEN depar = 'e-team' THEN pops END) AS "E-team"
FROM testbed.OrderingTAB
GROUP BY location
ORDER BY location;

How it works: #

• We group all rows by location so each location gets a single row in the result.
• For each team (from depar), the CASE WHEN checks if the row matches that team, then returns the pops value.
• The aggregation function (MAX()) picks out the non-null value for each team in the group, since only one row per location-team pair exists.
• ORDER BY location ensures your results are sorted the way you showed in your example.

Using PIVOT (For Databases That Support It) #

If you're using SQL Server, Oracle, or PostgreSQL (with an extension), the PIVOT operator makes this even cleaner:

SQL Server/Oracle Version: #

SELECT 
    location,
    [a-team] AS "A-team",
    [b-team] AS "B-team",
    [c-team] AS "C-team",
    [d-team] AS "D-team",
    [e-team] AS "E-team"
FROM (
    SELECT location, depar, pops
    FROM testbed.OrderingTAB
) AS SourceTable
PIVOT (
    MAX(pops)
    FOR depar IN ([a-team], [b-team], [c-team], [d-team], [e-team])
) AS PivotTable
ORDER BY location;

PostgreSQL Version (Requires tablefunc Extension): #

First enable the extension if you haven't already:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then use the crosstab function:

SELECT *
FROM crosstab(
    'SELECT location, depar, pops FROM testbed.OrderingTAB ORDER BY 1,2',
    'SELECT unnest(''{a-team,b-team,c-team,d-team,e-team}''::text[])'
) AS ct(location text, "A-team" bigint, "B-team" bigint, "C-team" bigint, "D-team" bigint, "E-team" bigint)
ORDER BY location;

All these solutions will give you the transposed, grouped result you need—just pick the one that fits your database setup!

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