Beyond Rows and Columns: Pivoting Techniques for String Data in T-SQL

Example:

Let's say you have a table named Orders with columns for CustomerID, OrderDate, and ProductCategory. You want to find out the number of orders placed for each product category.

Here's a T-SQL query demonstrating how to achieve this using PIVOT:

SELECT CustomerID,
       [Beverages] = COUNT(*)
FROM Orders
PIVOT
(
  COUNT(*)
  FOR ProductCategory IN ([Beverages], [Electronics], [Clothing])
) AS PivotTable;

In this example:

• CustomerID is retained as a row identifier.
• The PIVOT clause calculates the count of orders for each distinct product category value (Beverages, Electronics, and Clothing).
• The resulting table displays the CustomerID and the count of orders for each specified product category in separate columns.

Points to Consider:

• Pivoting is effective when you have a limited number of distinct string values in the column you are pivoting on.
• For a dynamic number of string values, you can explore alternative methods like conditional aggregation or more advanced techniques.

This code builds upon the scenario described earlier, where you want to find the number of orders placed for each product category.

CREATE TABLE Orders (
  CustomerID INT,
  OrderDate DATE,
  ProductCategory VARCHAR(50)
);

INSERT INTO Orders (CustomerID, OrderDate, ProductCategory)
VALUES (1, '2023-10-25', 'Beverages'),
       (1, '2023-11-18', 'Electronics'),
       (2, '2023-10-30', 'Clothing'),
       (2, '2023-11-10', 'Beverages'),
       (3, '2023-11-15', 'Electronics');

SELECT CustomerID,
       [Beverages] = COUNT(*),
       [Electronics] = COUNT(*),
       [Clothing] = COUNT(*)
FROM Orders
PIVOT
(
  COUNT(*)
  FOR ProductCategory IN ([Beverages], [Electronics], [Clothing])
) AS PivotTable;

DROP TABLE Orders;

This code first creates a sample table Orders with relevant columns. Then, it inserts some example data. The main query uses the PIVOT operation to calculate the count of orders for each specified product category ("Beverages", "Electronics", and "Clothing"). The resulting table displays the CustomerID and the count of orders for each category. Finally, the table is dropped.

Example 2: Finding Maximum Product Price by Brand

This example demonstrates pivoting on a string value and using a different aggregate function (MAX).

CREATE TABLE Products (
  ProductID INT,
  Brand VARCHAR(50),
  ProductName VARCHAR(100),
  Price DECIMAL(10,2)
);

INSERT INTO Products (ProductID, Brand, ProductName, Price)
VALUES (1, 'Brand A', 'Headphones', 79.99),
       (2, 'Brand B', 'Laptop', 899.99),
       (3, 'Brand A', 'Speakers', 129.95),
       (4, 'Brand B', 'Tablet', 249.99),
       (5, 'Brand A', 'Smartwatch', 199.95);

SELECT Brand,
       [Maximum Price] = MAX(Price)
FROM Products
PIVOT
(
  MAX(Price)
  FOR ProductName IN ([Headphones], [Laptop], [Speakers], [Tablet], [Smartwatch])
) AS PivotTable;

DROP TABLE Products;

Similar to the first example, this code creates a sample table Products and inserts sample data. The query then pivots on the Brand column and uses the MAX function to find the maximum price for each specified product name within each brand. The result displays the Brand and the maximum price for each product name listed in the PIVOT clause. Finally, the table is dropped.

This approach uses a CASE statement within the SELECT clause to categorize data based on the string column values. Then, it groups the data and applies aggregate functions for each category.

Consider the same scenario from the first pivoting example where you want to count orders by product category. Here's how you can achieve it using conditional aggregation:

SELECT CustomerID,
       SUM(CASE WHEN ProductCategory = 'Beverages' THEN 1 ELSE 0 END) AS Beverages,
       SUM(CASE WHEN ProductCategory = 'Electronics' THEN 1 ELSE 0 END) AS Electronics,
       SUM(CASE WHEN ProductCategory = 'Clothing' THEN 1 ELSE 0 END) AS Clothing
FROM Orders
GROUP BY CustomerID;

• The CASE statement checks for each product category value and assigns a value of 1 if the condition is true (0 otherwise).
• The SUM function aggregates the results for each category within each customer group.

This method is flexible as you can add more conditions for additional categories without modifying the core structure of the query. However, it can become cumbersome with a large number of categories.

Dynamic SQL with FOR XML PATH:

This method involves constructing the PIVOT query dynamically using string concatenation and the FOR XML PATH clause. It's suitable for situations where the list of categories is stored elsewhere or can be generated dynamically.

Note: This method is generally less performant than traditional pivoting and requires more advanced T-SQL knowledge.

Example (Conceptual):

1. Imagine you have a separate table listing all possible product categories.
2. Use a cursor to iterate through each category.
3. Within the loop, dynamically construct the PIVOT clause using string concatenation based on the current category.
4. Execute the complete PIVOT query for each iteration.