Question

You are building a reporting view in a Fabric warehouse. The source table Sales has columns OrderId, ProductCategory (values 'Electronics', 'Clothing', 'Toys'), and Amount. You need a single result row per unused grouping that returns one column per category with the total Amount for that category, so the report can display categories side by side. You want to do this in a single T-SQL query without a dataflow. Which approach should you use?

Accepted Answer

Use SUM(CASE WHEN ProductCategory = 'Electronics' THEN Amount END) and similar conditional aggregates, one per category, in a single SELECT with GROUP BY.

Answer

Use SELECT DISTINCT on ProductCategory and join the table to itself three times on OrderId.

Answer

Use the UNPIVOT operator on the Amount column grouped by ProductCategory.

Answer

Use STRING_AGG(Amount, ',') grouped by ProductCategory to combine values into one column.