I have an MS SQL Server table that looks like this:

Example SQL table structure

I have employees in each row, some columns that describe them and then columns where I have Quota and Achievement for fiscal quarters.

As you can guess, new fiscal quarters will be added, the employees won't be the same for each quarter.

For me, to use the data in Tableau, this would be the desired format:

Desired format

I would like to keep the employees as separate lines, their descriptive columns as columns, but aggregate Quota and Achievement into one column and separate rows for each individual quarter.

I could have a list of possible fiscal quarters which will appear as columns later if an easy solution requires that.

I have been reading about dynamic pivots as I guess that will be my solution but didn't manage to achieve this format as I'm very new to the UNPIVOT function.

Any guidance would be much appreciated

CodePudding user response：

This is a heavily denormalized structure, but if that's what you have to work with...

You can unpivot all columns by first aggregating them using FOR JSON, then selecting the ones you want back into separate rows using OPENJSON

SELECT
  t.EmployeeID,
  upv.*
FROM YourTable t
CROSS APPLY (
    SELECT t.*
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(json)
CROSS APPLY (
    SELECT
      [Fiscal Quarter] = LEFT(j2.[key], 7),
      Quota = SUM(CASE WHEN j2.[key] LIKE '%Quota' THEN CAST(j2.value AS int) END),
      Achievement = SUM(CASE WHEN j2.[key] LIKE '