Simple way to transpose columns and rows in SQL?

Transpose essentials: Digging deeper

Dynamically flipping tables in SQL

When the number of columns is dynamic, constructing a static query is impractical. Enter Dynamic SQL, where the SQL statement is crafted as a string and executed. This journey involves a pit stop at information schema views to fetch metadata dynamically for your pivot query.

Check out this gist:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Unleash column names (treat this spot as your genie lamp)
SELECT @columns = STRING_AGG(QUOTENAME(column_name), ', ') 
FROM information_schema.columns
WHERE table_name = 'YourGenieLamp' AND data_type IN ('Rub', 'It', 'Quick');

-- Let the magic carpet ride begin
SET @sql = N'
SELECT *
FROM YourGenieLamp
PIVOT (
  MAX(value)
  FOR column_name IN (' + @columns + ')
) AS PivotOnlyOnRequest;';

EXEC sp_executesql @sql;


Mixing aggregation and string concatenation

Want to transposes specific rows to columns? Use STRING_AGG or GROUP_CONCAT to join values separated by a defined delimiter. Wrap it with an aggregation function like the humble tortilla does for your favorite burrito filling, to reshape your data into a single appetizing row per group.

SELECT grouping_col,
  STRING_AGG(value, ', ') WITHIN GROUP (ORDER BY value) AS `tasty_burrito`
FROM table
GROUP BY grouping_col;


Coaxing rows to columns with Common Table Expressions

Common Table Expressions (CTEs) are the Swiss Army knives in your SQL toolkit. They allow you to define a temporary result set and refer to it in your following SELECT or PIVOT operation. Break down your transposition mission into smaller, manageable quests.

WITH CTE AS (
  SELECT id, attribute, value
  FROM table
)
SELECT id,
MAX(CASE WHEN attribute = 'Height' THEN value END) AS Height,  -- How tall?
MAX(CASE WHEN attribute = 'Width' THEN value END) AS Width       -- How wide? No, not the chicken's side!
FROM CTE
GROUP BY id;


Beyond the basics: Advanced transposition tactics

Unleashing the power of cross join

For fixed data sets, a CROSS JOIN with a values table can pivot your data faster than you can say "SQL". This is the card up your sleeve when you have a defined set of data to rotate with no need for dynamic column assembly.

Choose simplicity, young padawan

If your journey simply requires a simple row-to-column transformation, resist the allure of complex SQL features. The Force is strong with those who understand their data structure before choosing their SQL weapon.

Prioritize performance and accuracy

Always test the speed of your transposition queries using real data, and don't forget to COUNT() twice, transposition once. Validate the transposed data against the original set to ensure no values decided to pull a Houdini during the transformation. And remember, GROUP BY is your loyal ally when using aggregation during SQL operations.

Weaponizing database-specific methods

Each RDBMS might have its own secret weapons to hasten the transposition process. Investigate the documentations of your specific database; you might discover a game-changing method or function.