如何在phpMyAdmin中横向展示MySQL大量键值对查询结果？

Hey there, based on your problem where you need to turn the vertical key-value results from SELECT FieldName, FieldValue FROM table into a horizontal layout—without relying on static PIVOT (which doesn't scale for thousands of records or dynamic field values)—here's a robust dynamic SQL solution:

The Problem #

Your current query returns rows like (name, john), (surname, doe), (age,25) vertically, but you want a single row with columns name, surname, age and values john, doe, 25. Static PIVOT won't work here because it requires hardcoding all column names, which isn't feasible for large datasets or dynamic FieldName values.

Dynamic SQL Fix #

This approach automatically detects all unique FieldName values and builds the pivot query on the fly:

-- Initialize variable to hold dynamic SQL
SET @sql = NULL;

-- Generate CASE statements for every unique FieldName
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN FieldName = ''',
      FieldName,
      ''' THEN FieldValue END) AS `',
      FieldName,
      '`'
    )
  ) INTO @sql
FROM `your_table`; -- Replace with your actual table name

-- Build the full query (replace `group_id` with your actual grouping column)
SET @sql = CONCAT('SELECT ', @sql, ' FROM `your_table` GROUP BY `group_id`');
-- Important: `group_id` is the column that ties related fields together (e.g., a user ID that links name/surname/age to the same user)

-- Execute the dynamic query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Key Notes #

• Grouping Column is Mandatory: You must have a column that groups related fields (like a user ID or record ID). Without this, all your data will be merged into a single row, which isn't what you want. If your table doesn't have this column, you'll need to adjust your data structure first.
• Scalability: This works for thousands of records because it dynamically adapts to all unique FieldName values—no manual column listing required.
• phpMyAdmin Usage: Just paste this code into phpMyAdmin's SQL query editor, replace the placeholders (your_table and group_id), and run it. You'll get the horizontal layout you're looking for.

Why Not Static Pivot? #

Static pivot queries (or manually written CASE statements) require you to explicitly list every possible FieldName as a column. This is impossible to maintain when you have thousands of records or when FieldName values change over time. Dynamic SQL solves this by automating the column generation.

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