How to perform a SQL Pivot on non-numeric data that spans across multiple rows?

edit

Pivoting non-numeric data that spans across multiple rows in SQL can be challenging, but it can be done using the following steps:

1. Identify the columns to include in the pivot: Choose the column(s) that will be used as row headers and the column(s) that will be used as column headers. These columns should be non-numeric.

2. Use a recursive CTE to create a row number for each distinct value in the row header column. This will allow us to group the values by row when pivoting.

3. Use a self-join to combine the rows that belong to the same group. This will create a single row for each group that includes all the values that should be pivoted.

4. Use the PIVOT function to pivot the data based on the column header values.

Here is an example query that pivots non-numeric data that spans across multiple rows:

WITH RecursiveCTE AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY RowHeader ORDER BY SortOrder) AS RowNum,
    RowHeader,
    ColumnHeader,
    ColumnValue
  FROM PivotingTable
), CombinedRows AS (
  SELECT
    RC1.RowHeader,
    RC1.RowNum,
    RC1.ColumnHeader,
    RC1.ColumnValue,
    RC2.ColumnHeader AS ColumnHeader2,
    RC2.ColumnValue AS ColumnValue2
  FROM RecursiveCTE RC1
  LEFT JOIN RecursiveCTE RC2
  ON RC1.RowHeader = RC2.RowHeader AND RC1.RowNum = RC2.RowNum - 1
), PivotTable AS (
  SELECT
    *
  FROM CombinedRows
  PIVOT (
    MAX(ColumnValue2)
    FOR ColumnHeader2 IN ([Column1], [Column2], [Column3])
  ) AS PivotTable
)
SELECT
  RowHeader,
  [Column1],
  [Column2],
  [Column3]
FROM PivotTable

In this example, we assume that we have a table named PivotingTable with the following columns: RowHeader, ColumnHeader, ColumnValue, and SortOrder. The RowHeader column contains the values that will be used as row headers in the pivot table. The ColumnHeader column contains the values that will be used as column headers in the pivot table. The ColumnValue column contains the values that will be pivoted. The SortOrder column is used to order the rows within each group.

The query starts by using a recursive CTE to assign a row number to each distinct value in the RowHeader column. It then uses a self-join to combine the rows that belong to the same group. Finally, it uses the PIVOT function to pivot the data based on the ColumnHeader values.

Note that the column headers must be hard-coded in the PIVOT statement. This means that you must know the possible values of the ColumnHeader column in advance. If the possible values are unknown or dynamic, you may need to use dynamic SQL to generate the pivot statement dynamically based on the actual values.