SQL Convert all rows of data in a specific column to columns and mapping values in another column to the new columns

Question

THE SOURCE TABLE Looks like this:

h1_ind = 1 indicates a row that contains column names.

Source                              
    col1    col2        col3     col4       h1_ind  table_sheet y   w
    Route   CNG/Diesel  Freq     Weekly Miles   1   Summary   2021  Week 1
    a        5          B        6              2   Summary   2021  Week 1
    b        1          1        1              3   Summary   2021  Week 1
    c        5          B        6              4   Summary   2021  Week 1  
    d        1          1        1              5   Summary   2021  Week 1
    Route   CNG/Diesel  Freq     Weekly Miles   1   Summary   2021  Week 2
    a        5          B        6              2   Summary   2021  Week 2
    b        1          1        1              3   Summary   2021  Week 2
    c        5          B        6              4   Summary   2021  Week 2  
    d        1          1        1              5   Summary   2021  Week 2

Then I convert to this: (FOR WEEK 1 ONLY)

Data                
IN      column           table_sheet     y      w     value h1_ind
    col1    Route        Summary        2021    Week 1  a   2
    col2    CNG/Diesel   Summary        2021    Week 1  5   3
    col3    Freq         Summary        2021    Week 1  B   4
    col4    Weekly Miles Summary        2021    Week 1  6   5
    col1    Route        Summary        2021    Week 1  b   2
    col2    CNG          Summary        2021    Week 1  1   3
    col3    Freq         Summary        2021    Week 1  1   4
    col4    Weekly Miles Summary        2021    Week 1  1   5
    col1    Route        Summary        2021    Week 1  c   2
    col2    CNG/Diesel   Summary        2021    Week 1  5   3
    col3    Freq         Summary        2021    Week 1  B   4
    col4    Weekly Miles Summary        2021    Week 1  6   5
    col1    Route        Summary        2021    Week 1  d   2
    col2    CNG          Summary        2021    Week 1  1   3
    col3    Freq         Summary        2021    Week 1  1   4
    col4    Weekly Miles Summary        2021    Week 1  1   5

I would like to be able to present as desired below.
(FOR WEEK 1 ONLY)

Desired output:
table_sheet    y      w      Route CNG/Diesel  Freq  Weekly Miles
Summary       2021   Week 1   a       5         B     6
Summary       2021   Week 1   b       1         1     1
Summary       2021   Week 1   c       5         B     6
Summary       2021   Week 1   d       1         1     1

Desired Mapping

how can I do this? Pivot only gives me one row (MAX, MIN) I need all rows to presented.

Answer 1

You can use a PIVOT, the key is to make sure h1_ind is also part of the grouping and not being aggregated.

• We use a windowed aggregate to get the header names for each set of values
• Unpivot using CROSS APPLY
• Then aggregate back up using PIVOT.

WITH Headers AS (
    SELECT *,
      Col1Header = MAX(CASE WHEN h1_ind = '1' THEN col1 END) OVER (PARTITION BY table_sheet, y, w),
      Col2Header = MAX(CASE WHEN h1_ind = '1' THEN col2 END) OVER (PARTITION BY table_sheet, y, w),
      Col3Header = MAX(CASE WHEN h1_ind = '1' THEN col3 END) OVER (PARTITION BY table_sheet, y, w),
      Col4Header = MAX(CASE WHEN h1_ind = '1' THEN col4 END) OVER (PARTITION BY table_sheet, y, w)
    FROM YourTable t
),
Unpivoted AS (
    SELECT
      t.table_sheet,
      t.y,
      t.w,
      t.h1_ind,
      v.Header,
      v.Value
    FROM Headers t
    CROSS APPLY (VALUES
        (Col1Header, Col1),
        (Col2Header, Col2),
        (Col3Header, Col3),
        (Col4Header, Col4)
    ) v(Header, Value)
    WHERE h1_ind > 1
)
SELECT *
FROM Unpivoted
PIVOT (
    MAX(Value) FOR Header IN
    (Route, [CNG/Diesel], Freq, [Weekly Miles])
) pvt;

db<>fiddle

You could also use MAX(CASE conditional aggregate here if you want, instead of PIVOT.

WITH ...
SELECT
  t.table_sheet,
  t.y,
  t.w,
  Route          = MAX(CASE WHEN v.Header = 'Route'        THEN v.Value END),
  [CNG/Diesel]   = MAX(CASE WHEN v.Header = 'CNG/Diesel'   THEN v.Value END),
  Freq           = MAX(CASE WHEN v.Header = 'Freq'         THEN v.Value END),
  [Weekly Miles] = MAX(CASE WHEN v.Header = 'Weekly Miles' THEN v.Value END)
FROM Unpivoted
GROUP BY
  t.table_sheet,
  t.y,
  t.w
  t.h1_ind;