-3

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

ghgma6
  • 1
  • 1
  • 1
    Images are discouraged for many reasons - better to include that information as a script containing DDL and sample data. Your table is already pivoted. You need to do the opposite of pivot - unpivot to convert columns to rows (for one emp/quarter per row). Perhaps now is a good time to reconsider this schema choice. – SMor Nov 01 '21 at 16:16
  • I am not the database owner, just a user of the data, unfortunately, it is not my merit to reconsider the original setup – ghgma6 Nov 01 '21 at 16:21
  • Which version of SQL Server do you have? – Charlieface Nov 01 '21 at 16:42
  • @Charlieface, we have an MS SQL Server – ghgma6 Nov 01 '21 at 17:21
  • Which version of SQL Server, run `select @@version` – Charlieface Nov 01 '21 at 19:53

2 Answers2

1

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 '%Achievement' THEN CAST(j2.value AS int) END)
    FROM OPENJSON(j.json) j2
        -- [key] is the column name
    WHERE j2.[key] LIKE '%Quota' OR j2.[key] LIKE '%Achievement'
    GROUP BY
      LEFT(j2.[key], 7)
) upv

You can do a similar idea with FOR XML on older versions of SQL Server

Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

I have reverted to a simple solution instead of the dynamic one which will require some maintenance from my side once a year, but it is a quick solution nevertheless.

I have queried the data for each quarter individually, and with a CASE statement creating a new field, called Fiscal Quarter and I entered the fiscal quarter value manually. I have renamed the respective columns to Quota and Achievement, ignoring the fiscal quarter reference in their name.

with a UNION ALL the queries are combined and I have a long query but the right table structure.

thank you all who looked into it

ghgma6
  • 1
  • 1
  • Would you mind sharing the relevant parts of your code as well? Your explanation is useful, but the basic structure of your SQL statement will be most useful to future readers struggling with a similar problem. – Jeremy Caney Nov 02 '21 at 00:01