Unpivot with column name

sql

sql-fiddle

pivot-tables

performance-optimization

byNikita Barsukov·Oct 1, 2024

To transform column data into rows, use the CROSS APPLY method along with a VALUES clause in SQL Server. For PostgreSQL, the UNNEST method alongside ARRAY serves the same purpose. Here are succinct examples:

SQL Server:

SELECT ID, Attrib, Val
FROM YourTable
CROSS APPLY (VALUES ('Col1', Col1), ('Col2', Col2)) AS CA(Attrib, Val) -- "Unpivot and clear the cobwebs from your columns"

PostgreSQL:

SELECT ID, c.Attrib, v.Val
FROM YourTable, UNNEST(ARRAY['Col1', 'Col2']) AS c(Attrib), UNNEST(ARRAY[Col1, Col2]) AS v(Val)
WHERE c.tableoid = v.tableoid -- "Sweeping away redundancy, one row at a time"

Replace Col1, Col2 with your column names and YourTable with your actual table name. This simple process essentially transposes columns into rows, allowing you to retrieve name-value pairs from your data.

Managing nulls, duplicates, and performance (The Cleanup Crew)

Handling null values

To ensure data integrity and get rid of any ghostly nulls, apply the WHERE clause to exclude rows where the column values are null:

SELECT ID, Attrib, Val
FROM YourTable
CROSS APPLY (
  VALUES 
  ('Col1', Col1), 
  ('Col2', Col2)
) AS CA(Attrib, Val)
WHERE Val IS NOT NULL -- "Null shall not pass!"

Salvaging repeated data

CROSS JOIN alongside a CASE expression can create multiple rows from a single one, assigning specific values (goodbye, duplicates!):

SELECT St.StudentName, S.Subject, 
CASE S.Subject 
  WHEN 'History' THEN St.History
  WHEN 'Math' THEN St.Math
  WHEN 'Science' THEN St.Science
END AS Marks
FROM StudentMarks St
CROSS JOIN (
  VALUES ('History'), ('Math'), ('Science') -- "Because '', '', '' would be too confusing"
) AS S(Subject)
WHERE St.[Subject] IS NOT NULL -- "Nulls, you shall not pass (again)!"

Improving performance

With larger data sets, your queries may feel like a snail race. Improve performance by:

Slimming down results: Use WHERE clauses to exclude unnecessary rows.
Saving energy: Use temporary tables or table variables to store intermediate results.
Fine-tuning speed: Use selective indexing on columns used in joining or filtering.

Best practices and handy tips (Keep 'em in your toolbox)

Diversify your methods

There’s more than one way to skin a cat. Or in this case, to unpivot your data. Plugins for alternative methods include: PIVOT functions, simple joins, or scalar functions. Have them in your toolbox for varying data situations.